Return Page Item From an AJAX Callback Process

Recently, I was working on a form that had multiple items retrieved when a select list item changed. The select list triggered a dynamic action that executed some PL/SQL code and returned item values.

It was looking like that:

At some point, the process became "too big" for the "Execute PL/SQL Code" action (it can only hold up to 4000 characters).

So, I decided to move everything to a page "AJAX Callback" process which can hold up to 32767 characters.

So I replaced the dynamic action "Execute PL/SQL Code" action with an "Execute JavaScript" action as follow:

/* Show a processing image */
var lSpinner$ = apex.util.showSpinner();

apex.server.process("SOME_PROCESS",
                    {pageItems: "#P2300_LOV"
                     },
                    {success: function(pData) {
                        /* If the AJAX is successful set the value or the returned items */
                        if (pData.success === true){
                            /* Loop through the array and set the value of each item */
                            for (var i=0; i < pData.items.length; i++){
                                apex.item(pData.items[i].id).setValue(pData.items[i].value);
                            }
                        }
                        
                        /* Remove the processing image */
                        lSpinner$.remove();
                      },
                     error: function(request, status, error) {
                        alert(request.responseText);
                         
                        /* Remove the processing image */
                        lSpinner$.remove();
                      }
                     }
                    );

And I created the AJAX Callback as follow:

declare
    /* Local variables */

    /* Utility function to output item's id and value */
    procedure output_json_item(p_item_name  in varchar2,
                               p_item_value in varchar2
                               )
    as
    begin
        apex_json.open_object;
        apex_json.write('id', p_item_name);
        apex_json.write('value', p_item_value, true); /* true so that null values are written as well */
        apex_json.close_object;
    end output_json_item;
begin
    /* ********************** *
     * PL/SQL Process Content *
     * ********************** */
    if :P2300_LOV = '1' then
        :P2300_ITEM1 := 1;
        :P2300_ITEM2 := 1;
        :P2300_ITEM3 := 1;
        :P2300_ITEM4 := 1;
    elsif :P2300_LOV = '2' then
        :P2300_ITEM1 := 2;
        :P2300_ITEM2 := 2;
        :P2300_ITEM3 := 2;
        :P2300_ITEM4 := 2;
    elsif :P2300_LOV = '3' then
        :P2300_ITEM1 := 3;
        :P2300_ITEM2 := 3;
        :P2300_ITEM3 := 3;
        :P2300_ITEM4 := 3;
    end if;
        
    apex_json.open_object;
    apex_json.write('success', true);
    
    apex_json.open_array('items');

    /* Call the utility procedure for every item you want to return */
    output_json_item('P2300_ITEM1', :P2300_ITEM1);
    output_json_item('P2300_ITEM2', :P2300_ITEM2);
    output_json_item('P2300_ITEM3', :P2300_ITEM3);
    output_json_item('P2300_ITEM4', :P2300_ITEM4);
    
    apex_json.close_array;
    
    apex_json.close_object;
exception
    when others then
        apex_json.open_object;
        apex_json.write('success', false);
        apex_json.write('message', sqlerrm);
        apex_json.close_object;
end;

The idea here is to have PL/SQL AJAX Callback execute and if it successfully completes, it will be returning the list of items along with their values as JSON on which we are going to loop through and set the items' value using the JavaScript item API.

You can have a look at my Demo Application

Securing Ajax Callback Process

When navigating from a report page to the corresponding detail page, it's always a good practice to enable the parameters checksum to prevent users from tampering with the item values that are in the url.

To enable arguments checksum, you must first set the "Page Access Protection" attribute under the page attribute security section to "Arguments must have checksum" and then for each item that is going to be assigned from the url, you need to set the "Session State Protection" to one of the "Checksum Required" value (have a look at the item's help for more details about the different types of checksum).

That way the user is not going to be able to change any value from the url.

I recently had to implement a similar concept using button in a report that were calling an AJAX Callback Process from a dynamic action.

Here's what my report's SQL Query looked like:

select '<button onclick="void(0);"'
           || ' data-data1="' || some_table_id || '"'
           || ' data-data2="' || some_value || '"'
           --|| ' data-data3="' || sys.dbms_crypto.hash(utl_raw.cast_to_raw(some_table_id || some_value || to_char(nvl(last_update_date, creation_date), :DATETIMEFORMAT)), 3) || '"' /* Where 3 -> SHA-1 from sys.dbms_crypto.hash_sh1 */
           || ' data-data3="' || apex_util.get_hash(apex_t_varchar2(some_table_id, some_value, to_char(nvl(last_update_date, creation_date), :DATETIMEFORMAT))) || '"'
           || ' class="t-Button t-Button--hot t-Button--small actionButton"'
           || ' type="button"><span class="t-Button-label">Some Action</span></button>'
       /* Rest of the query */
  from some_table
 where /* where clauses */

Where data1 and data2 are the values that are needed in the AJAX Callback Process and data3 is the checksum for the previous values.
To have a stronger checksum and to ensure that it is only used once, I added the record's last update date to it.
DATETIMEFORMAT is an application substitution string.

Note: in order to be able to use dbms_crypto, it needs to be granted to the current user (not required with the apex_util.get_hash function.
For more information on the Apex API, read this GET_HASH Function.

The corresponding dynamic action to handle the button click was as follow:

Dynamic Action: Event: Click
jQuery Selector: ".actionButton"

var lSpinner$ = apex.util.showSpinner();
var lReportRegion$ = $(this.affectedElements);

apex.server.process("AJAX_PROCESS_NAME",
                    {x01: $(this.triggeringElement).data('data1'),
                     x02: $(this.triggeringElement).data('data2'),
                     x03: $(this.triggeringElement).data('data3')
                     },
                    {success: function( pData ) {
                        if (pData.success === true){
                            /* Show Sucess Message */
                            showSuccessMessage(pData.message);
                            
                            /* Refresh Region */
                            lReportRegion$.trigger('apexrefresh');
                        }
                        else{
                            /* Show Error Message */
                            showErrorMessage([pData.message]);
                        }
                        
                        lSpinner$.remove();
                      }
                     }
                    );

In the previous code, I'm using the affected element attribute to refresh my report region.

Here's the AJAX Callback Process:

declare
    l_some_table_id number;
    l_some_value    varchar2(50);
    l_checksum      varchar2(32767);
    --l_checksum      raw(32767); --sys.dbms_crypto.hash returns raw
    --l_hash_type     pls_integer := sys.dbms_crypto.hash_sh1;
    
    l_last_update   varchar2(100);
begin
    /* Retrieve parameters */
    l_some_table_id := to_number(apex_application.g_x01);
    l_some_value    := apex_application.g_x02;
    l_checksum      := apex_application.g_x03;
    
    select to_char(nvl(last_update_date, creation_date), :DATETIMEFORMAT)
      into l_last_update
      from some_table
     where some_table_id = l_some_table_id;
    
    /* Validate the checksum */
    --if l_checksum = sys.dbms_crypto.hash(utl_raw.cast_to_raw(l_some_table_id || l_some_value || l_last_update), l_hash_type) then
    if l_checksum = apex_util.get_hash(apex_t_varchar2(l_some_table_id, l_some_value, l_last_update)) then
        /* Do something */
        
        apex_json.open_object;
        apex_json.write('success', true);
        apex_json.write('message', 'Action Processed.');
        apex_json.close_object;
    else
        apex_json.open_object;
        apex_json.write('success', false);
        apex_json.write('message', 'Invalid Action.');
        apex_json.close_object;
    end if;
exception
    when no_data_found then
        apex_json.open_object;
        apex_json.write('success', false);
        apex_json.write('message', 'Invalid Action.');
        apex_json.close_object;
    when others then
        apex_json.open_object;
        apex_json.write('success', false);
        apex_json.write('message', 'Invalid Action.');
        apex_json.close_object;
end;

Basically what we do in the process is to recreate a checksum based on the parameters' values and to compare it with the checksum parameter and if they match we execute the code that needs to run.

Note: Edited above code to use the Apex API: apex_util.get_hash. Commented out everything related to dbms_crypto for reference purpose.