Preceding Execution Chains
On an APEX application page, I’ve established a straightforward procedure to mimic a possibly longer-running process by utilizing sys.dbms_session.sleep. This procedure doesn’t employ the new Execution Chain; rather, it’s a standard Execute Code process type designed to execute the specified PL/SQL code. The duration of the sleep is determined by the user, and the process is activated upon clicking a button.
With this conventional process type, when the process executes, it operates in the foreground, and any post-processing actions such as branches are postponed until the process finishes. Usually, a spinner is shown to indicate that the process is ongoing.
Execution Sequences
Execution Chain processes are established using a similar approach to other process types, like triggering them on Page Render (Pre-Rendering or Post-Rendering), on Page Submit (After Submit or Processing), or on demand as an AJAX Callback.
Once created, you can link other child processes, such as PL/SQL processes, to it. An ‘Add Child Process’ option is available in the right-click context menu for Execution Chain types.
When defining your Execution Chain, one crucial attribute to initially consider is:
Settings Run in Background This setting determines whether to execute the chain in the background or not. If ‘Run in Background’ is activated, several other attributes become available:
Settings Return ID to Item This allows you to select a page item to return the execution ID of the chain into. This facilitates checking the status of the background execution. Context Value Item Here, you choose a page item containing a Context Value to be passed to the background execution. Temporary File Handling To execute chains in the background, APEX clones the current APEX session, and all background executions work with this session clone. This attribute specifies how to handle temporary files uploaded via a File Browse page item:
- Ignore: Choose this if the background processes will not work with the uploaded file.
- Move: Select this option if access to temporary files is needed only by one background execution chain.
- Copy: Choose this option if multiple background execution chains require access to temporary files. Executions Limit This attribute limits the number of background executions a user can request in the current session. Submit Immediately Turn this attribute on if the background process should be submitted for execution immediately, outside of the current transaction. This ensures that if the transaction is rolled back, the submitted execution remains in the queue. Serialization Serialize When set to “On,” all executions (from all APEX sessions) with the same Context Value are serialized to avoid concurrent executions in the background. An execution with a NULL context value will block executions regardless of the context value. A new execution with a NULL context value will run only if no other execution of this process is running.
Use this setting when processes of this chain are likely to work on the same resources, such as performing DML on the same row in a table.
After creating the Execution Chain, you can incorporate child processes into it. These processes are generated in the standard manner but are linked to the Execution Chain through the ‘Execution Chain’ attribute.
Auxiliary Entities
To bolster Execution Chains, Oracle offers a view (APEX_APPL_PAGE_BG_PROC_STATUS) and an API (APEX_BACKGROUND_PROCESS).
The APEX_APPL_PAGE_BG_PROC_STATUS view furnishes details about the submitted Execution Chain, including Status, Status Message, Progress (SOFAR), and Total Work (TOTALWORK) yet to be accomplished.
The APEX_BACKGROUND_PROCESS API includes the following functions/procedure:
- GET_CURRENT_EXECUTION: Returns status information of the current running background execution.
- GET_EXECUTION: Provides status information for an execution with a specific execution ID.
- SET_PROGRESS: Allows programmatic setting of the execution’s progress (SOFAR) against the total work to be done (TOTALWORK).
- SET_STATUS: Enables programmatic setting of the Status Message for an execution.
- TERMINATE: Offers two signatures—one to terminate a specific execution chain based on ID and another to terminate all executions of an execution chain. The TERMINATE procedures replace the deprecated ABORT procedures.
Presentation
I devised a basic Execution Chain set to run in the background, returning the execution ID to a Global Page Item named P0_EXECUTION_ID. Additionally, I restricted users to three executions.
I established a couple of child processes, both executing similar functions, a sample of which is outlined below. Similar to my prior foreground process, these processes rely on the sleep time determined by user input. Moreover, I utilized the API to:
- Define the status message using the SET_STATUS procedure.
- Specify the current progress against the total workload using the SET_PROGRESS procedure.
Additionally, within my application, I’ve developed the following:
- A report utilizing APEX_APPL_PAGE_BG_PROC_STATUS to track the progress of my execution chain.
- A dynamic action for periodically refreshing my report.
- An application process for monitoring the progress of my execution chain via the GET_EXECUTION function. This process is based on the Execution ID returned to the P0_EXECUTION_ID page item, which provides the current state.
A Global Page Dynamic Action runs upon page load to execute JavaScript that calls the application process mentioned earlier (utilizing apex.server.process). It generates a notification if the process completes successfully. Therefore, the message is displayed during the initial load of an application page after the execution chain successfully concludes, while the user navigates through the application.
In contrast to my conventional foreground process, in the application, the user initiates the new Execution process by setting a sleep time and clicking a button to submit the page.
However, since the process is now running in the background, the branch to another page triggers immediately without waiting for the process to finish. The destination page is the report page that illustrates the progress of my process.
The report intermittently refreshes, displaying the relevant Status Message for each stage in the chain and the progress made against the total workload (refer to the three screenshots below).
Refresh 1:
Refresh 2:
Refresh 3:
If the user attempts to trigger four of these execution chains simultaneously within the same session, surpassing the allowed limit of three, an error message will be shown.
Subsequently, as the user navigates through the application, a notification is displayed on the initial application page load following the successful completion of the process.
Wrap-Up
The inclusion of the Execution Chain process in the APEX toolkit significantly streamlines application navigation during the execution of background processes. With the accompanying supporting view and API, monitoring the progress of these processes and reporting on their status is much simpler compared to the necessity of using scheduled jobs prior to APEX 23.1.