Sometimes you need to perform sequential background processing in APEX - but the processing could take longer than what is convenient for a page submit. During the processing it would also be user friendly to show the progress, as each row is processed. There any many ways to achieve this (database scheduler jobs, APEX Execution chains, Automations…). This article describes a solution using APEX AJAX requests, performing background processing (sequentially) for a number of rows in an interactive grid, using apex.server.process. The solution processes all rows in the grid, but can easily be adjusted to process only selected rows.
The benefits of doing this type of sequential processing are:
Background processes are not competing for resources
System load will not be heavily affected.
Setting up the interactive grid
The interactive grid is a simple editable grid based on the DEPT table:
The following customisations are done:
Static id = dept.
All columns are Read Only = Always (except STATUS)
Adding a column named STATUS which is a Textfield, with Source = None and Appearance CSS classes = is-readonly (we want to be able to set this column programatically, but not allow users to change this).
Toolbar for the grid is disabled
The STATUS column will be used to display the progress of the processing.
Setting up the processing
The background processing is defined in an AJAX Process on the page. This will be running on the database server when triggered by apex.server.process.
The process is named “PROCESS_DEPARTMENT” and the Execution Point is “Ajax callback”:
declare
l_deptno dept.deptno%type := apex_application.g_x01;
begin
-- Process department
dbms_session.sleep(3);
-- Return result
apex_json.open_object();
apex_json.write('deptno', l_deptno);
apex_json.write('status', 'OK');
apex_json.close_object();
end;
The serverside process can either get input parameters from APEX page items, or from x01,x02… parameters. In this example we will use apex_application.g_x01 to pass in the deptno.
AJAX processes must (by default) output a valid JSON response. This is achieved using the APEX_JSON PL/SQL package. You can also just use the htp.p procedure to construct the JSON yourself. In the above example we want to pass back the deptno that has been processed, and the status of the processing.
The dbms_session.sleep command should be replaced with any relevant processing code you need (most often a call to a PL/SQL package procedure or function).
To kick off the processing, we need a new button (in my example this is placed in the Breadcrumb region). The button is called PROCESS, and the Static id for this is also PROCESS (this is important when we want to disable/enable this). The button should action is Defined by a dynamic action, which has a single javascript action :
let lSpinner$ = apex.util.showSpinner( $( "body" ) );
let model = apex.region("dept").widget().interactiveGrid("getViews", "grid").model;
let totalRecords = model.getTotalRecords();
let totalProcessed = 0;
// Disable the button
$('#PROCESS').prop('disabled', 'disabled');
// Loop through all records
model.forEach(function(row) {
// Set in progress for record
model.setValue(row, 'STATUS', '...');
var result = apex.server.process( 'PROCESS_DEPARTMENT', {
x01: model.getValue(row, 'DEPTNO')
},
{
queue: {
name: "processList",
action: "wait"
}
} );
result.done( function( data ) {
// do something here
totalProcessed++;
// Set processed status
let record = model.getRecord(data.deptno);
model.setValue(record, 'STATUS', data.status);
// Remove spinner on last job
if (totalProcessed >= totalRecords) {
lSpinner$.remove();
model.clearChanges();
// apex.region('dept').refresh();
apex.message.showPageSuccess( 'All departments has been processed');
$('#PROCESS').prop('disabled', '');
}
} ).fail(function( jqXHR, textStatus, errorThrown ) {
if (textStatus != 'abort' && jqXHR.responseText) {
lSpinner$.remove();
$('#PROCESS').prop('disabled', '');
apex.message.clearErrors();
// Now show new errors
apex.message.showErrors( [
{
type: "error",
location: "page",
message: jqXHR.responseText,
unsafe: false
}
] );
}
});
});
The code loops through all records in the model behind the interactive grid. If you only want to process the selected rows, look for the getSelectedRecords method. Each row starts a call to apex.server.process passing in the DEPTNO in the x01 parameter.
By default all AJAX request would be sent to the server and started simultaneously and run in parallel, however this is not what we want here. The queue option helps us to define this as a processing where each AJAX request waits for the previous to complete:
queue: {
name: "processList",
action: "wait"
}
The wait action is described as “This action is the default and is used to send requests one after the other. When the action is wait, the request is added to the named queue. If there are no other requests in that queue in progress or waiting, then this request is executed.”
The name property specifies the name of the queue to add this request to.
The effect of this code, is that the loop will execute 4 times (since we have 4 records in the DEPT table), and 4 AJAX requests will be placed in the processList queue. When one AJAX request completes, the next is started.
Error handling
So whats happens when one of the server side processes fails ? Lets say the second process (out of 4) fails (PL/SQL raises an exception):
Success
Fails
Aborted
Aborted
Any subsequent queued AJAX call is aborted. That is why the errorhandling code tests for textStatus != 'abort' - because we only want to display an error from process 2, and not for process 3 and 4 (which are aborted).
Download sample code
The code described is based on the EMP/DEPT sample tables, and the background processing is simulated by calling the dbms_session.sleep() procedure. This can be easily adapted to a real business application scenario.
The sample APEX application can be downloaded from github, and can be installed in an APEX 24.1 (or later) instance. The APEX application file is : apex/f103_practical.sql.
Any feedback or improvements are welcomed.
/Martin