Forcing APEX_APPLICATION.G_Fx values into session

When working with the APEX_ITEM package to programmatically generate items on the screen, either from a dynamic PL/SQL region or within a report region, you will most likely want to make use of any values entered into those items within your PL/SQL submit processes.

Application Express provides the APEX_APPLICATION.G_F01 to G_F50 arrays for this purpose. Each item you programmatically create is given an index value between 1 and 50 which directly corresponds to a G_Fx array temporarily containing the value (or values if multiple iterations of an item are generated) when the page submits. These can then be easily referenced in your PL/SQL code.

A problem arises however if you want to reference those array values before the page has submitted, for instance within PL/SQL in a dynamic action. Because the values are only held temporarily when you submit, they cannot be referenced as part of the dynamic action.

This is a problem that we encountered recently on a customer project. The particular page we were developing featured a report of employees. Each employee had a checkbox which could be selected and then a remove button could be used to remove checked rows from the report. The requirement was to remove the rows without refreshing the screen (dynamic actions), but also to validate each checked row via a PL/SQL function prior to their removal, based on the current state of data elsewhere in the database. Because of the dynamic nature of the call to the database any attempt to access the APEX_APPLICATION.G_FX arrays was unsuccessful.

The solution we used makes use of the apex.server.process JavaScript function introduced with Apex 4.2. The apex.server.process function allows you to call an on-demand AJAX process within JavaScript. The same thing could previously be accomplished prior to 4.2 with the undocumented htmldb_Get function. The apex.server.process function is documented in the Apex Javascript API reference and one of its features is that it allows you to pass values to the APEX_APPLICATION.G_FX arrays. Below is a simple example of how this works.

First we create a SQL report on a page. The SQL should be similar to the following. Note that we are using index number 1 for the checkbox values.

Next, we create a button that will fire a Dynamic Action on click. Within the Dynamic Action we create an “Execute JavaScript Code” action with the following code.

Finally, we create an AJAX Callback PL/SQL process (right-click “AJAX Callbacks” at the bottom of the Page Processing column of the edit page screen) called “Process Checked Rows”. Below is an example process that simply inserts the checkbox values into a table called checkbox_test.

When the button is clicked, the JavaScript passes the values to the “Process Checked Rows” callback process which inserts any checked ID’s into the database.

Author: Craig Sykes 

Job Title: Senior Oracle Development Consultant

Bio: Craig is a Senior Development Consultant at DSP-Explorer. Craig has an MSc in Computing Science and is an experienced software engineer, utilising development tools such as PL/SQL and APEX to provide bespoke ERP software to both UK and international businesses. Craig has experience developing solutions to connect Oracle systems to a wide range of existing external applications within business environments.