APEX 23.1 execution chains greatly expand the power of your page processes. This new process type lets you assign a name to a group of child processes that will execute in sequence if the parent’s condition is true. Any of the chain’s child processes can be another chain if needed, so you can organize page logic into a more self-documenting tree structure.
In addition, you can configure any branch of this “processing tree” to run in the background with the flick of a switch in the Property Editor. Your end-users move on to tackle other tasks in your app while longer-running work happens behind the scenes. In this article, you’ll learn more about this new feature, how to try out a sample app, and where to download the slides from my recent APEX Alpe Adria conference presentation about it.
Chains Enable Increased Clarity
APEX developers know that their page processes execute in sequence. But many page processes, like the three in the figure below, have a Server-side Condition configured. When a teammate opens a page like this, to understand the “flow” of the page logic they must select each conditional page process and study its conditional expression.
The figure below shows how introducing two execution chains with meaningful names helps to clarify the intent of the processing logic. If we are rendering the page in order to duplicate a Venue record, then two of the page processes indented inside that “branch” of the tree will execute. Otherwise, the Initialize from Venue process will run. Notice how we’ve “refactored” common conditions on the individual page processes to instead be associated with the owning parent chain.
Defining an Execution Chain
To define an execution chain, just create a page process and set its type to Execution Chain. To add child processes to it, choose Add Child Process from the chain’s right-click context menu. As shown in the figure below, for any page process you select in the component or processing tab, its Execution Chain property shows the name of the execution chain it belongs to, or displays None if it is a top-level page process. To change which execution chain a page process belongs to, simply use this Execution Chain select list in the Property Editor and change it to the new parent execution chain name.
Switching On Background Execution
While APEX Automations let you schedule periodic background jobs, beyond sending email they are a essentially a code-focused affair. Execution chains extend your processing capabilities deeper into the declarative realm. You can organize page processes of any type into chains whose sequence of child processes either run immediately in the user session or else in the background. A chain can send an email, send a push notification, perform data loading, initiate a human approval task, execute PL/SQL code, or engage any other page process type, including custom plug-in types. Used with the InvokeAPI page process type, it offers a simple way to orchestrate the conditional execution of APIs implemented in PL/SQL packages or as REST services.
When running in the background, your chain’s child processes automatically have access to a cloned copy of all the session state, and they can update page item values in the usual way. You can configure the chain to move selected temporary files to the background session, or to make a copy if both the foreground and background need to access a file for some reason. The only difference to be aware of is that, by design, changes to session state performed by page processes running in the background are not reflected in the end user’s application session that originally initiated the background execution chain.
A Deeper Processing Tree from the Sample
The figure below shows the processing tree of the Department page in the sample app we explore below. Notice how we can read the processing logic like an outline to more easily understand what it’s doing. After first processing the Department form, if the (Process) button was pressed, it first checks whether the current department is being processed already. If it’s not being processed the Else if Deptno Available to Process… chain runs. It starts by registering the username who is kicking off the processing of the current department. Then it proceeds to run the Process Department in Background chain. Notice in the Property Editor that the Execute in Background switch is turned on for this chain. It’s configured to return the unique ID of the execution background process into the P3_EXECUTION_ID page item.
Once the background processing is enqueued — which happens very quickly — the current chain proceeds to execute the next child process at the same level: If Background Process Succeeded… This process can test whether the value of P3_EXECUTION_ID is not null to conclude that enqueuing the background work was successful. Finally, the Close Dialog process closes the modal dialog. In short, the rule of thumb is that every page process at the same level of the tree runs sequentially, with background chains getting enqueued to be executed as soon as is feasible. We explore below why that might not be immediately, due to limits imposed on concurrent background jobs.
Overview of the Sample App
The short video demonstration below shows off the sample app for this article. The Departments page lets you open a modal drawer to edit the department. That Department page also contains a (Process) button to trigger the execution of the When Process Button Pressed… action chain shown above. This action chain’s “tree” of child processes ultimately contains the Process Department in Background action chain that is configured to run in the background. The Processing Status page shows the progress of each background process. The Processing History page shows average running time of the background processes, and the average time background processes wait before starting.
What’s Going on in Process Department?
The long-running Process Department process in the sample calls the PL/SQL procedure PROCESS_DEPTNO in the EBA_DEMO_BG_PROC package using InvokeAPI. This code simulates a long-running process that will take between 60 and 100 seconds. Notice it uses the SET_PROGRESS() procedure in the APEX_BACKGROUND_PROCESS package to report progress of the background processing to the APEX engine. The Processing Status page references this information about units of total work and units completed so far using the APEX dictionary view APEX_APPL_PAGE_BG_PROC_STATUS.
procedure process_deptno(p_deptno number)
is
c_total_secs constant integer := 60 + round(dbms_random.value(0,40));
c_steps constant integer := 10;
begin
-- Simulate long-running process taking 60-100 secs
apex_background_process.set_progress(p_totalwork => c_steps,
p_sofar => 0);
for j in 1..c_steps loop
dbms_session.sleep(round(c_total_secs/10));
apex_background_process.set_progress(p_totalwork => c_steps,
p_sofar => j);
end loop;
end; Imposing Limits on Background Processing
APEX lets developers set a per-session limit on the number of times an end user can simultaneously enqueue a background execution chain. You accomplish this by setting a value for the Executions Limit property of the chain. It also allows configuring both an application-level and workspace-level maximum number of concurrent background processes. The per-session limit will raise an error if a user tries to exceed the limit. In contrast, the limit on concurrent executions throttles the processing speed of the background execution chain jobs queue. As shown in the demo video above, background processes may have to wait longer to get scheduled and execute. To show the difference, the figure below shows the same sample app running on an APEX instance where the concurrent background processing limit is at least three or higher. You can see that the user scheduled three DEPT rows to be processed and they are all in some stage of execution progress simultaneously.
An obvious result of a system with higher resource limits is that enqueued background execution chain jobs get scheduled and execute more quickly. As shown in the figure below, the average time a job waits to start is around one second on the system I used. In the demo video I recorded using apex.oracle.com, the average wait-to-start time was over a minute where resources were more constrained.
Handling Failure to Enqueue Background Process
On page 3 in the sample app, the Process Department in Background execution chain is configured with an Executions Limit property value of 3. This means a user session can initiate a maximum of three background execution chain jobs. An attempt to launch a fourth one will produce the error shown in the figure below.
We configure the error message using the Error Message property on the execution chain. However, this sample application inserts a row into the table EBA_DEMO_BG_PROC_PROCESSES to track which user is processing which department, and later the background execution chain process updates that row with the execution chain id once the background job gets scheduled and begins to run. In the case that the user encounters an error while the background execution chain is enqueued due to having hit the Executions Limit, I need to delete the tracking row in the EBA_DEMO_BG_PROC_PROCESSES table that had been inserted previously. I accomplished this by registering the page 3 error handler function to use the p3_handle_bg_processing_error function below (in the eba_demo_bg_proc package). It checks whether the error encountered relates to the Process Department in Background process, and if so, it calls unregister_bg_proc_for_deptno() to delete the tracking row.
function p3_handle_bg_processing_error(p_error apex_error.t_error)
return apex_error.t_error_result is
l_result apex_error.t_error_result;
begin
l_result := apex_error.init_error_result(
p_error => p_error);
if is_process_error(p_error,'Process Department in Background') then
unregister_bg_proc_for_deptno(
p_deptno => V('P3_DEPTNO'),
p_orig_id => V('P3_ONGOING_WORK_ID'),
p_success => false
);
end if;
return l_result;
end; Getting the Sample App
Download the sample app from here. Before running the application, first ensure you’ve installed the DEPT/EMP sample dataset. If you’re not sure, choose SQL Workshop > Utilities > Sample Datasets to verify and install it if needed.
Downloading My Slides
You can download the slides from my recent talk on this subject at the APEX Alpe Adria 2023 conference from here: APEX 23.1: Native Push Notifications & Easy Background Processing.
Dive Into APEX 