Showing posts with label APEX. Show all posts
Showing posts with label APEX. Show all posts

Monday, March 31, 2014

Running Custom Code for Tabular Forms (Part 1)

One of my biggest pet peeves with Tabular Forms in APEX is that it would only run basic (Insert, Update, Delete) DML functions against a table. This works really well for basic situations but more often than not data must be processed by a procedure to handle all the business logic. For this reason, I've avoided Tabular Forms for a very long time.

Last week at OGh APEX World, Dimitri Gielis showed how you can run your own procedure against a tabular form. Here's how to do it:

First, create a Tabular Form using the standard wizard. This will create the standard validations and processes for the page. Their will be two Page Processing processes as shown below.

These processes will automatically handle any of the data changes that a user makes in the Tabular Form. For now you can go ahead and remove each process as we'll use a custom procedure to process the data instead.

Next, create a new process. The important part comes when creating the process; be sure to select the Tabular Form option (as shown below).

In your PL/SQL block, you can now reference each of the columns using their column names (example :SAL and :ENAME). What's even better is that APEX will only run the code against rows that have changed which can save a lot of processing time. For example, if only two rows in a 15 row table were changed the code will be executed twice.

In the next article I'll show how to expand this functionality beyond a base table and use this new technique to modify any data set.

Monday, February 24, 2014

APEX Shortcuts

The other day I was dabbling around in APEX and noticed a link for Shortcuts in the Shared Components section.

I’ve never used Shortcuts before (let along knew about them) so I tried it out. To start here’s how Shortcuts are described (as copied from APEX screen):

Shortcuts are a repository of shared static or dynamic HTML. Shortcuts are substitution strings that are expanded using the syntax: "SHORTCUT_NAME". Shortcuts are used in the following locations:

  • Region Source for regions of type HTML_WITH_SHORTCUTS
  • Region Templates, Region Headers & Footers
  • Item Labels
  • Item Default Value
  • Item Post Element Text
  • Item Help Text
  • HTML Header of a page

Creating shortcuts on page item labels and page item post element text attributes can include the following substitution strings: 

To reference Shortcuts you need to use the “shortcutname” syntax (quotes included). Since they are wrapped in quotes and could conflict with regular text I strong recommend using a naming scheme such as SC_NAME.

Note: I previously wrote an article about the different ways to reference APEX variables. I have updated it to include Shortcuts. The article is available here.

Shortcuts can either be statically defined or reference a PL/SQL function. It’s important to note that if you do call a PL/SQL function it will execute the code each time the Shortcut is referenced. For example, if you have the same Shortcut in three different regions on a page it’ll call the function three times. This may be a good or bad thing depending on how you use it.

Though I haven’t found an immediate need for Shortcuts I think there could be some situations where it can come in handy for labels and templates especially since it allows you to reference a function which can dynamically generate content.

Saturday, February 8, 2014

APEX Theme Competition

In case you haven't heard, ODTUG is hosting an APEX Theme Competition. This is your chance to show off what you can do and also win some amazing prizes including a Kscope 15 pass!

For more information go to

Wednesday, January 15, 2014

Changing Default Item Required Error Message in APEX

In APEX, if a page item is a required value it is no longer necessary to create an explicit validation. Setting the item's option "Value Required" to Yes will make it a required field as shown below.

This is much quicker then manually creating Not Null validations for each required item as required in older versions of APEX.

If the page is submitted and the item is null, then the page will be reloaded with a validation error message stating that "" The default message can me changed by doing the following:

  • Go to Shared Components > Text Messages (under Globalization)
  • Create Text Message
    • Text: #LABEL# can not be null
      • Use what ever message you'd like to see.
      • Note that the substitution string #LABEL# is used for the item's label.
When the page is now submitted and the item is null, the page will be reloaded with a validation error message stating that "". 

The above technique changes the default message for all items that have their Value Required option set to Yes (i.e. it's an application level setting).

Monday, January 13, 2014

Stopping Dynamic Actions using apex.da.cancelEvent

Update: Nick Buytaert wrote a follow-up article on the apex.da.resume function. I suggest reading it along with this article in case you need to resume a Dynamic Action (which is especially useful for plugins).

They're a few times when you may need to stop a Dynamic Action part way through the executing all its actions. Since individual actions don't have conditions associated with them there's no supported way to do this. Thankfully there's an undocumented JavaScript (JS) function called apex.da.cancelEvent. Here's an example of how to use it:

Suppose you have a page with two items, P1_X and P1_Y, and a button on P1. When the user clicks the button the Dynamic Action (DA) needs to set P1_X = X and P1_Y = Y. The DA setup will look like the following:

When you click the button P1_X and P1_Y are set to X and Y respectively. 

Now suppose that after P1_X is set you wanted to stop all other actions in the DA (in this case setting P1_Y). There's no way to declaratively do this. Instead you must use the apex.da.cancelEvent function. 

Add a new JavaScript "True Action" to the existing DA with sequence 15 (so its between the two other actions). In the code section use:;  Be sure to uncheck the "Fire On Page Load" option. The updated DA will look like image below (the new action is highlighted).

If you refresh P1 and click the button associated to this DA only P1_X will be set. The third action in this DA will not be executed.

The above example probably isn't the best use case for leveraging apex.da.cancelEvent however it does highlight its functionality. It is useful if you create your own custom confirm notice or some other process that determines if the rest of the actions in a DA can occur.

Note: You'll notice that I used the additional call() function when calling apex.da.cancelEvent in the action. Calling it defines the value of this in the function call as it is required in the cancelEvent function. For more information please read the JS call() documentation.

Monday, January 6, 2014

Derived Columns in APEX Standard Reports

A while back I wrote an article about a feature in APEX's standard reports called Derived Columns. Despite using APEX for a very long time I had never really noticed it or used it before. Someone asked for some more information on derived columns, so this is a follow up article with a bit more detail.

Derived columns are place holders for an additional column in the report. The column can reference any of the query's columns but has some limitations such as not declaratively supporting links or being about to compute a value. It's most useful when you need an additional column to be displayed in a specific HTML format.

In the past when I wanted an additional column for display purposes I would just add it in the query as "..., null my_additional_column ... " and then modify the column's HTML Expression value. Now I can just a derived column and modify its HTML Expression.

To add a derived column, go to the Report Attributes then click Add Derived Column on the right hand side as shown below:

After clicking the above link a new column will appear in the list of columns.

You'll need to edit the newly added derived column and add something in the Column Formatting > HTML Expression field for values to appear. Tip: to reference columns in the HTML Expression field use the #COLUMN_NAME# notation. 

Thursday, August 30, 2012


The APEX Dictionary is a set of views that describe all the different objects of an APEX application. They are extremely useful when trying to compare objects or using the metadata in your application. One example, which I recently wrote about, is to use a view from the dictionary to leverage the APEX build options in your PL/SQL code.

By default the views will only allow you to see information for applications, and their objects, that are linked to your current schema (i.e. the application's parsing schema must be the same as your schema). For older versions of APEX the only way to view all the applications in the entire database was to either log in as SYSTEM or SYS.

In newer versions of APEX (I think it was released in APEX 4.1) there's a new database role called APEX_ADMINISTRATOR_ROLE. This role allows for non SYSTEM/SYS users to view all the APEX applications in your database. It's a very useful thing to have if you want to run your own scripts to check for things like standards, security audits, performance, etc.

One example where this role can be very useful is to monitor for slow running pages in all your applications across the entire database (rather than just ones in a particular schema). The following query, executed by a user that has the APEX_ADMINISTRATOR_ROLE, will show all the slow pages in the past two days:
FROM apex_workspace_activity_log
WHERE trunc(view_date) >= trunc(SYSDATE) - 1 -- Just look at the past 2 days
  AND elapsed_time > 1; -- 1 = 1 second
This is just one of many examples where the APEX_ADMINISTRATOR_ROLE can be
useful for system wide level analysis.

The APEX_ADMINISTRATOR_ROLE also allows you to run procedures in the APEX_INSTANCE_ADMIN package.

Tuesday, August 28, 2012

How to Send/Upload a CLOB from the Browser to APEX via AJAX

Today Alistair Lang asked on Twitter "how do I pass in a CLOB to an on-demand process using AJAX in APEX?". I had this same questions a few months ago when I was working on uploading files using AJAX into APEX.

It turns out you can't use the standard addParam APEX JavaScript method (hopefully this will change in 4.2). Instead you need call a different function which will store the CLOB into a special APEX collection then process the CLOB from that collection. Here's a breakdown of what needs to happen:

- Send the CLOB from the browser to APEX. It will be stored in the CLOB001 column in the collection "CLOB_CONTENT".
- Once the CLOB is sent to APEX call your On Demand process (i.e. AJAX request) to run some PL/SQL code. This PL/SQL code will need to retrieve the CLOB value from the collection

Here's an example

On Demand Process: On your page create an On Demand process called "MY_PROCESS". In the code enter the following:
  l_clob CLOB;
  SELECT clob001 
  INTO l_clob
  FROM apex_collections 
  WHERE collection_name = 'CLOB_CONTENT';
  -- Now you can process the CLOB using l_clob
JavaScript Code: This can be stored either in a Dynamic Action or custom JS code:
 * Code to run once the upload is done
 * Clob is now accessible in the apex_collections view:
 * SELECT collection_name, seq_id, clob001 FROM apex_collections 
 * WHERE collection_name = 'CLOB_CONTENT';
 *  - Note: The collection name "CLOB_CONTENT" is not modifiable
 * Use this function to make an AJAX request to trigger 
 * an On Demand Process (i.e. run some PL/SQL code)
function clubUploadDone(){
  var get = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=MY_PROCESS',$v('pFlowStepId'));
  //Optional: pass some additional values get.addParam('x01','some data');
  gReturn = get.get();

 * Send clob to APEX (will be stored in the apex_collection "CLOB_CONTENT"
var clobObj = new apex.ajax.clob(
  //Callback funciton. only process CLOB once it's finished uploading to APEX
    if (p.readyState == 4){
clobObj._set('Put clob content here'); //Sends the data to Oracle/APEX collection

It's important to note that there is only one area where the CLOB data is stored so each time you send a new CLOB it will erase the older value in the collection. If you're sending multiple CLOBS sequentially you need to handle it accordingly. A good example of this is if you're uploading multiple files via a drag & drop interface.

Tuesday, August 21, 2012

Integrating Build Options in your PL/SQL Code

Build Options is a great tool to enable or disable certain objects in APEX. If you've never used them before or don't know what they are I suggest you read Scott Wesley's article.

I typically use Build Options to display data for developers that should not be displayed in production. This can help improve development time since you don't have at include, then remove, code in an application before it is migrated to production.

Build Options are great for APEX but what about PL/SQL code that is directly associated with an APEX application? You could enable and disable certain page processes with build options but that doesn't allow "fine grained Build Option" control in your PL/SQL code.

Thankfully you can view your Build Options, and their status, with an APEX Dictionary view:  APEX_APPLICATION_BUILD_OPTIONS.

Here is a function that I wrote that will allow you to easily see if a given Build Option is enabled in your PL/SQL code:
 * Returns Y or N if a build option is enabled
 * @param p_build_option_name Name of build option (case sensitive)
 *  - You can change this to not be case sensitive if applicable
 * @param p_app_id Application ID, default current Application ID
 *  - Included this as a parameter in case testing from straight PL/SQL
 * @return Y or N
 * @author Martin Giffy DSouza
 * @created 15-Aug-2012
create or replace FUNCTION f_is_build_option_enabled(
  p_build_option_name IN apex_application_build_options.build_option_name%TYPE,
  p_app_id IN apex_application_build_options.application_id%TYPE DEFAULT nv('APP_ID'))
  return varchar2
  l_build_option_status apex_application_build_options.build_option_status%type;

  SELECT upper(build_option_status)
  into l_build_option_status
  FROM apex_application_build_options
  WHERE application_id = p_app_id
    AND build_option_name = p_build_option_name;
  IF l_build_option_status = 'INCLUDE' THEN
    RETURN 'Y';
    RETURN 'N';
  WHEN others THEN
    -- Your call on how to handle errors
Here's an example of how you can use it in your PL/SQL code. Note that it doesn't pass in the application id since it is assuming that you're executing the PL/SQL as part of the APEX application:
IF f_is_build_option_enabled(p_build_option_name => 'DEV_ONLY') = 'Y' THEN
  htp.p('DEV_ONLY build option enabled');
  htp.p('DEV_ONLY build option disabled');

Wednesday, August 15, 2012

How to Create an APEX Session in PL/SQL

Debugging a function or procedure which references APEX items using the V/NV functions or trying to debug APEX collections can be frustrating when the only way to set, and view, the values is to actually do it through the application. Thankfully, there's a way to create an APEX session from a PL/SQL session to test out your code.

They're various examples on the APEX forums on how to create an APEX session in PL/SQL. Here's one version of it.

CREATE OR REPLACE PROCEDURE sp_create_apex_session(
  p_app_id IN apex_applications.application_id%TYPE,
  p_app_user IN apex_workspace_activity_log.apex_user%TYPE,
  p_app_page_id IN apex_application_pages.page_id%TYPE DEFAULT 1) 
  l_workspace_id apex_applications.workspace_id%TYPE;
  l_cgivar_name  owa.vc_arr;
  l_cgivar_val   owa.vc_arr;

  l_cgivar_name(1) := 'REQUEST_PROTOCOL';
  l_cgivar_val(1) := 'HTTP';
    num_params => 1, 
    param_name => l_cgivar_name, 
    param_val => l_cgivar_val ); 
  SELECT workspace_id
  INTO l_workspace_id
  FROM apex_applications
  WHERE application_id = p_app_id;


  apex_application.g_instance := 1; 
  apex_application.g_flow_id := p_app_id; 
  apex_application.g_flow_step_id := p_app_page_id; 

    p_uname => p_app_user, 
    p_session_id => null, -- could use APEX_CUSTOM_AUTH.GET_NEXT_SESSION_ID
    p_app_page => apex_application.g_flow_id||':'||p_app_page_id); 
To create an APEX session (in PL/SQL) to mimic some tests I can do the following:
  2    sp_create_apex_session(
  3      p_app_id => 106,
  4      p_app_user => 'MARTIN',
  5      p_app_page_id => 10);
  6  END;
  7  /

PL/SQL procedure successfully completed.
View some APEX session state variables:
SQL> SELECT v('APP_USER') app_user, v('APP_SESSION') app_session,
  2    v('APP_PAGE_ID') page_id, v('P1_X') p1_x
  3  FROM dual;

---------- --------------- ---------- ----------
MARTIN     374363229560201 10

SQL> -- Set P1_X
SQL> exec apex_util.set_session_state('P1_X', 'abc');

PL/SQL procedure successfully completed.

SQL> SELECT v('APP_USER') app_user, v('APP_SESSION') app_session,
  2    v('APP_PAGE_ID') page_id, v('P1_X') p1_x
  3  FROM dual;

---------- --------------- ---------- ----------
MARTIN     374363229560201 10         abc

SQL> -- Clear APEX Session State
SQL> exec APEX_UTIL.CLEAR_APP_CACHE(p_app_id => 106);

PL/SQL procedure successfully completed.
You can also create and view collections using the APEX_COLLECTION APIs and the APEX_COLLECTION view.

Saturday, April 28, 2012

Command Line Backups for APEX Applications

One thing developers like to do is keep local backup copies of their development work. Sometimes they'll copy files and suffix them with .bak or they'll store them in a version control repository. With APEX you don't copy a file to back it up. Either you have to copy the application (within the workspace) or export the application and save it locally. For one-off backups this can be ok, but as you develop with more and more applications in larger environments it can be tedious work and will slow down your development time.

Thankfully APEX has a tool, that comes as part of the install zip file, which allows for command line exports. It's one of the lesser known tools that is extremely useful.

Below is a simplified copy of a batch file which will automatically backup all the APEX applications on a given database using the APEX backup tool. I usually recommend that organizations run such a script on their development environments to store hourly backups of their development environments. The example is a very simple example which should be modified to tag the backup with the date/time or zip and store the backups in version control. I've also appended an extra script which lists all the APEX applications, workspaces, and IDs and stores this information as part of the backup in a .html file.

To use the script, create the files listed below in the same directory and make the appropriate modifications in apex_backup.bat

Backups all the applications in the database

REM ***** Parameters ******
REM Oracle information
REM To find SID name: select * from v$instance;
set oracleSID=XE
set oracleSystemPass=oracle
set oraclePort=1526
set oracleHost=localhost
REM Root directory where oracle\apex\*class files are stored
REM These is a sub directory from the extracted APEX install zip file
set apexExportJavaDir=C:\temp\apex_4.1.1_en\apex\utilities
REM File that contains names of applications and workspaces
set apexHtmlFileName=apex_info.html
REM Directory where backups will be stored
set backupLocation=C:\temp\apexbackup

REM ****** PATHS *********
REM Note: You may not need to explicitly define these as they may already be set in OS.
set ORACLE_HOME=C:\oracle\product\11.2.0\client_1
set CLASSPATH=%CLASSPATH%;.\;%ORACLE_HOME%\jdbc\lib\ojdbc5.jar;%apexExportJavaDir%
set PATH=%PATH%;.\;C:\Program Files (x86)\Java\jre6\bin


REM ****** Other *********
set startRunLocation=%CD%

REM ****** Directory Setup ******
REM Create temp bacpkup location
mkdir %backupLocation%

REM ****** APEX BACKUP *******
REM Go to backup location to run backups in
cd %backupLocation%

REM Export all applications
java oracle.apex.APEXExport -db %oracleHost%:%oraclePort%:%oracleSID% -user system -password %oracleSystemPass% -instance

REM Export all Workspaces
java oracle.apex.APEXExport -db %oracleHost%:%oraclePort%:%oracleSID% -user system -password %oracleSystemPass% -expWorkspace

REM Generate listing of Workspaces and Applications
sqlplus system/%oracleSystemPass%@%oracleHost%:%oraclePort%/%oracleSID% @%startRunLocation%\apex_backup_info.sql %apexHtmlFileName%

REM Back to start location
cd %startRunLocation%
Stores all the application names, workspaces, and IDs
-- Parameters
-- 1 = File Name

SET NUMFORMAT 9999999999999999999999999999999999999


-- Current Date
select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') current_date
from dual;

-- Applications
SELECT application_id, workspace, application_name, owner
FROM apex_applications
WHERE workspace != 'INTERNAL'
ORDER BY application_id;

-- Workspaces
SELECT workspace_id, workspace
FROM apex_workspaces
WHERE workspace != 'INTERNAL'
ORDER BY workspace_id;



Wednesday, July 13, 2011

How My Family Solves Computer Problems

In my talk, How to be Create II, at ODTUG Kscope 11 I discussed what sometimes happens when users try to fix problems themselves. This is a video clip from my presentation on how my family tries to resolve computer problems.

And yes it is a true story. I got those emails from my family...

Thanks to Chris Hritzuk for recording this.

Tuesday, July 12, 2011

How to be Creative II - Presentation Slides

You can download the slides of the How to be Creative II presentation that I gave at ODTUG Kscope 11 from the following link: Please note that the slides have been modified for handout purposes (i.e. not the exact same that I showed in the presentation).

The slides should also be updated soon on the ODTUG Kscope Scheduler:

Monday, July 11, 2011

APEX Region Errors - Part 3

Disclaimer: This is an advanced post that discusses and modifies some of the inner workings of APEX.

In APEX Region Errors - Part 2 I discussed how to add triggers on the APEX Activity Log tables to store information in custom error tables when a user encounters an APEX region error.

Instead of storing the information in custom error tables you can leverage the APEX Feedback tool and trigger an automatic feedback entry. This may be a preferred option as you don't need to create custom tables and the feedback tool provides a lot of information.

Before continuing it's important that you know how to use the new APEX Feedback tool. If you don't know about the APEX Feedback tool I suggest that you read about how to create the feedback link ( and how to access the feedback tool ( Try implementing it in a dummy application to see what you can do with it.

The following triggers will enter a feedback entry each time a region error occurs:

Disclaimer (again): Modifying anything in the APEX schema will put your APEX instance in an unsupported state. Please proceed with caution. I take no responsibility for any negative outcomes from this.

-- NOTE: Do this as the APEX_040000 user or a privlidged user such as SYSTEM
CREATE OR REPLACE TRIGGER apex_040000.trg_apex_activity_log1_air
ON apex_040000.wwv_flow_activity_log1$
-- Log as feedback
apex_util.submit_feedback (
p_comment => 'AUTO MSG: Region Error', -- Put a comment here that can be used to easily identify auto generated feedback messages
p_type => 3, -- Bug. See API documentation for different values
p_application_id => :new.flow_id,
p_page_id => :new.step_id,
p_email => null,
p_label_01 => 'Error Message', -- You can add up to 8 label/attributes. See API documentation for more information
p_attribute_01 => :new.sqlerrm,
p_label_02 => 'Component Type',
p_attribute_02 => :new.sqlerrm_component_type,
p_label_03 => 'Component Name',
p_attribute_03 => :new.sqlerrm_component_name

-- Could use APEX_MAIL to send a notification to a list of developers to take a look at problem
-- This is entirely optional. Modify as required
p_to => '',
p_from => '',
p_body => 'Region Error Occured. Please Check Feedback',
p_body_html => '',
p_subj => 'APEX Region Error');

And... (differences are highlighted)

-- NOTE: Do this as the APEX_040000 user or a privlidged user such as SYSTEM
CREATE OR REPLACE TRIGGER apex_040000.trg_apex_activity_log2_air
ON apex_040000.wwv_flow_activity_log2$
-- Log as feedback
apex_util.submit_feedback (
p_comment => 'AUTO MSG: Region Error', -- Put a comment here that can be used to easily identify auto generated feedback messages
p_type => 3, -- Bug. See API documentation for different values
p_application_id => :new.flow_id,
p_page_id => :new.step_id,
p_email => null,
p_label_01 => 'Error Message', -- You can add up to 8 label/attributes. See API documentation for more information
p_attribute_01 => :new.sqlerrm,
p_label_02 => 'Component Type',
p_attribute_02 => :new.sqlerrm_component_type,
p_label_03 => 'Component Name',
p_attribute_03 => :new.sqlerrm_component_name

-- Could use APEX_MAIL to send a notification to a list of developers to take a look at problem
-- This is entirely optional. Modify as required
p_to => '',
p_from => '',
p_body => 'Region Error Occured. Please Check Feedback',
p_body_html => '',
p_subj => 'APEX Region Error');

When a region error occurs you can now view the information in Team Development > Feedback.

If you click the Edit button you can get a lot of detailed information about was happening when the user encountered the error including all of the values in session state.

Thursday, July 7, 2011

APEX 4 + HTML 5 = Awesome: Presentation Slides

You can download the slides of the APEX 4 + HTML 5 = Awesome presentation that I gave last week at ODTUG Kscope 11 from the following link: Please note that the slides have been modified for handout purposes (i.e. not the exact same that I showed in the presentation).

The slides should also be updated soon on the ODTUG Kscope Scheduler:

I'll post the How to be Creative II slides in the next few days.

Wednesday, July 6, 2011

ODTUG KScope 11 - Recap

I had an amazing time in Long Beach CA last week for ODTUG Kscope 11. This was my fourth time attending the Kscope conference and I must say that the ODTUG committee, along with YCC, made it the best one yet!

It all started for me on Saturday. Once I arrived in Long Beach I went to register right away. Along with registration (which went flawlessly thanks to Lauren) I had my picture taken with Crystal in the new social media lounge. Lori, from YCC, did an excellent job promoting social media at Kscope 11 and I think it was a huge success.

Sunday was the symposium day. Not surprisingly I attended the APEX track which were presentations given by the Oracle APEX team on the upcoming 4.1 features in APEX. Mike Hichwa gave us a glimpse of some of the long term plans for APEX. There's some really cool things coming up that he discussed including an APEX App Store.

After all the presentations were done we had the Welcome Reception in the Grand Ballroom. It was really nice to catch up with everyone and meet a lot of new people.

Following the Welcome Keynote, the individual sessions started on Monday. It was really tough to chose which talk to go to. So many times throughout the conference I wanted to be in two or three places at the same time.

I really enjoyed Dimitri Gielis's presentation on APEX charts. He also mentioned that we may* (in the future) be able to have HTML 5 charts instead of flash charts. This would be very good for applications that run on iOS devices.

On Monday there was the APEX Open Mic night. This is something I was really looking forward to because you get to see a lot of live demos of APEX being used in organizations. Wayne Linton, a fellow Calgarian, kicked things off with a demo of his APEX application for managing roles in the database.

Following the Open Mic night James and Tom from RedGate took some of us out to dinner at Parker's Lighthouse. Great food and good times with those that were there.

Tuesday morning I hid away in my hotel room as I was doing some last minute prep and practice for my presentation. I gave my first talk, APEX 4 + HTML 5 = Awesome, right before lunch to a packed room. It went really well and was a lot of fun.

In the afternoon I went to Roel Hartman's XFiles presentation. It is a cool application for storing and managing files in the database using XML DB. You can even download the application on SourceForge:

The final session of the night that I attended was Dimitri's APEX in Big Projects with Many Developers. Dimitri showed how he, and the team at APEX Evangelists, develop their applications. It was really interesting to see the list of tools they use and why they use them. It also encouraged me to start tracking time I spend on non-billable items such as writing blog posts, plugins, and answering questions.

Following the presentation everyone went to the Grand Ballroom for Happy Hour where we had some drinks and appetizers.

In the evening I attended the Oracle ACE dinner put on by Lillian Buziak. It was my first ACE dinner and I was very excited to be there and meet some of the other ACEs. After dinner I had a drink with some of the usual suspects (you know who you are) at the hotel bar and then headed off to bed.

After Tuesday's long day and great dinner it was tough to get up on Wednesday morning, but I schleped through it. The first presentation that I attended was by John Scott on APEX 4 plug-ins. The room was packed and, as usual, John did an excellent job highlighting how plugins work and showed off a few that he built. It was really cool to see that John integrated one of the HTML 5 items that I had demoed in Tuesday's presentation into one of his plug-ins.

After the morning sessions I took the rest of the afternoon off to deal with a critical client request and catch up on some rest (I didn't get a lot of sleep over the past few days so it eventually caught up to me).

Wednesday night was the big social event aboard the Queen Mary. The ODTUG committee went all out; it was SPECTACULAR. There was food everywhere, open bar (I was on best behavior since I had to present the next morning), jazz band, dueling piano, poker room, boat tours, and a cover band on the deck. The evening finished with some fireworks. I don't know how they're going to be able to top this event next year but I'm sure they'll find a way :-).

Those that woke up on Thursday morning were pretty tired from the long week and the previous night's soiree. I gave my second presentation, called How to be Creative II, at 8:30 AM. A lot of people shockingly showed up and things actually went really well given that everyone was a bit groggy. I threw in a few extra jokes to help keep the audience awake. Dan McGhan was also giving a talk on Dynamic Actions at the same time. Unfortunately I wasn't able to attend (since I was presenting) but I heard it went really well.

I attended part of Raj Mattamal's presentation on Collections. Raj is a very "dynamic" speaker and always puts on an interesting talk. I also caught part of Shakeeb Rahman's talk where he converted an APEX application to mimic Apple's home page (he called it iToons). It was Shakeeb's first solo talk and he did an excellent job.

I also had a bit of a surprise on Thursday. At the closing ceremony I won the Best Speaker award for the APEX track and the overall Best Speaker of the Year award! I'm very honored to win the awards.

For those of you who didn't get to attend you really missed out on a stellar conference. The good news is that you can already register for Kscope 12 at which is going to be in San Antonio, Texas. Hope to see everyone there.

- Martin

Thursday, June 23, 2011

ODTUG Kscope 11

I'll be speaking at ODTUG Kscope 11 next week in Long Beach, California. I've attended this conference for the past three years and it's always been an amazing time. Some of the world's best Oracle and APEX developers will be there. One of the best things I found is that everyone is extremely friendly and very approachable.

Here are the talks that I'm giving this year:

APEX 4 + HTML 5 = Awesome
Tuesday June 28, 11:15 AM-12:15 PM, Room: 202C

HTML 5 consists of many new features which will make developers’ jobs a lot easier and improve end-user experience. HTML 5 is currently under development but many browsers already support some of its features. APEX developers should be aware of these enhancements. This presentation will highlight some of the HTML 5 features and how to integrate them with your APEX application to produce stunning results.

How to Be Creative II
Thursday June 30, 8:30 AM-9:30 AM, Room: 202B

This presentation will demonstrate how to leverage the APEX dictionary, along with existing plug-ins, to enhance your APEX applications. It will include how to automate monitoring of your application, improve testing capabilities, improve development time, and provide some tweaks to enhance end-user experience.

If things work out I'll be giving away copies of the two books I co-authored (Beginning Oracle
Application Express 4
and Expert Oracle Application Express) at each of my presentations (if this sounds like a bribe it is, especially for the Thursday morning presentation following the Wednesday night party :-). Hope to see you there!

Monday, June 13, 2011

Beginning Oracle Application Express 4 - Author Podcast

I recently recorded a podcast, along with some of the other authors, about writing Beginning Oracle Application Express 4. Here's some more information about the podcast:

A conversation with Patrick Cimolini, Martin D'Souza, and Doug Gault; Authors of Beginning Application Express 4.

David Peake, Principal Product Manager for APEX, chats with some of the authors of Beginning Application Express 4. They will discuss various aspects about the book including the main reason for the book, who the book is focused towards, and some behind the scene information about writing the book.

Wednesday, June 8, 2011

Highlight Words in Standard Reports

After creating a plugin I received a lot of questions about how to enable column highlighting as shown in the following screen shot:

Column highlighting is not part of the plug-in. It is a built-in feature in APEX that has been around for a very long time. They're two ways to enable column highlighting in standard reports. The first is through the create report wizard. When you enable searching, as shown below, the columns that are selected to be searchable will automatically have column highlighting enabled on then.

To manually highlight a column edit the column. Scroll down to the Column Formatting region shown below. In the Highlight Words field you can enter a comma delimited list of words to search for or reference APEX items using the &ITEM_NAME. syntax. Note: for more on APEX variable syntax read this post.

Wednesday, April 13, 2011

Malicious Code in APEX Plugins - Feedback

My previous post about Malicious Code in APEX Plugins identified the possibility of harmful code in plugins (if you haven't read it please read the post before continuing). Several people had some excellent feedback which they included in the comments section. This post summarizes their comments and provides my thoughts on them.

Oracle Maintaining Plugin Repository

The idea is for Oracle to host something similar to Apple's App Store so that all code must pass a set of standards etc. I don't work for Oracle so I can't comment on this too much and I think it's a good idea. That being said the current community plugin repository,, has an approval process before plugins are released.

Wrapped PL/SQL and Licensed Plugins

Some plugins have wrapped PL/SQL source code and obfuscated/minimized JavaScript (JS) code. Plugin developers may need to wrap their PL/SQL code since their plugins are licensed. They may also minimize their JS files for performance issues. When you use these plugins the company that developed it can help determine its legitimacy (more on this in the next section). Just because it's wrapped doesn't mean you should not install it. They're other ways to validate that it is safe to use.

Trusted Developers and Organizations

They're some companies and developers within the APEX community that are well known and trusted. These companies specialize in APEX and would never write malicious code. For example I would never hesitate to install a plugin from organizations such as (but not limited to) ClariFit, Apex Evangelists, APEX Freelancer, Skill Builders, and Sumneva.

Scalability and Upgrades

Scott Spendolini made a great comment about the scalability of plugins and upgrades. I think this has to be examined on a case by case basis. If you're using a plugin on a small application that doesn't get a lot of hits then it may be a moot point. If your application gets millions of hits a day and you use a poorly optimized plugin then maybe you need to modify it to fit your needs. When looking at the source code you may not only be looking for malicious code but also techniques to improve performance for your specific needs. If the plugin has wrapped PL/SQL you can try to contact the developer/company to address your specific needs.

Like all software, plugins may need to be upgraded as APEX evolves (and it's 3rd party add-ons like jQuery). If the plugin is open source you can easily modify the code or email the developer with a change request. I've had several people email me about bugs and feature enhancements for plugins and was able to implement them in future versions.