APEX Execution Chain Process

Oracle APEX 23.1 introduced some process improvements, including a new process type: Execution Chain, which allows you to create a sequence of processes executed in the foreground or background. In this blog, I will demonstrate an introductory look into the Execution Chain process type.

Before Execution Chains

In an APEX application page, I have created a simple process to replicate a potentially longer-running process using sys.dbms_session.sleep. This process doesn’t use the new Execution Chain but is just a normal Execute Code process type to execute the stated PL/SQL code. The sleep time is user-defined, and the process is triggered by a button click.

With this more traditional process type, on the execution of the process, it runs in the foreground, and any post-processing actions e.g. branches, are delayed until the process is complete. Typically, a spinner will be displayed while the process is in progress.

Execution Chains

Execution Chain type processes are created in a similar way as other process types e.g., 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 associate other child processes, such as PL/SQL processes, with it. There is an ‘Add Child Process’ option in the right-click context menu for Execution Chain types.

In defining your Execution Chain, initially, there is a key attribute to consider:

• Settings
  • Run in Background
    • specify whether to execute the chain in the background or not.

If Run in Background is switched on, then several other attributes become available:

• Settings
  • Return ID to Item
    • Choose a page item to return the execution ID of the chain into. This will allow checking the status of the background execution.
  • Context Value Item
    • 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 will work with the session clone. This attribute determines how to deal with temporary files uploaded via a File Browse page item.
      • Ignore – choose this if the background processes will not work with the uploaded file.
      • Move – choose this option if access to temporary files is only needed by one background execution chain.
      • Copy - choose this option if there are multiple background execution chains, and they all need access to temporary files.
  • Executions Limit
    • Limits the number of background executions a user can request in the current session.
  • Submit Immediately
    • Switch this attribute on if the background process should be submitted for execution immediately, outside of the current transaction, so 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 will be serialized, to avoid concurrent executions in the background. An execution with a NULL context value will block executions regardless of the context value, and a new execution with a NULL context value will only run 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, for instance, performing DML on the same row in a table.

Once the Execution Chain has been created, you can add child processes to it. These are created in the normal way but then associated with the Execution Chain using the ‘Execution Chain’ attribute.

Supporting Objects

To support Execution Chains, Oracle has provided a view (APEX_APPL_PAGE_BG_PROC_STATUS) and API (APEX_BACKGROUND_PROCESS).

The APEX_APPL_PAGE_BG_PROC_STATUS view provides details of the submitted Execution Chain, including Status, Status Message, Progress made so far (SOFAR), and the Total Work (TOTALWORK) to be carried out.

The APEX_BACKGROUND_PROCESS API provides the following functions/procedure:

• GET_CURRENT_EXECUTION – returns status information of the current running background execution.
• GET_EXECUTION – returns status information for an execution of a specific execution ID.
• SET_PROGRESS – allows programmatic setting of the progress of an execution (SOFAR) against a total amount of work to be done (TOTALWORK).
• SET_STATUS – allows programmatic setting of the Status Message for an execution.
• TERMINATE – two signatures are available to either terminate a specific execution chain based on ID or to terminate all executions of an execution chain. The TERMINATE procedures replace the already deprecated ABORT procedures.

Demonstration

I created a simple Execution Chain to run in the background to return the execution ID to a Global Page Item P0_EXECUTION_ID. I limited the user to three executions.

I created a couple of child processes, both performing similar functionality, an example of which can be seen below. The processes, like my earlier normal foreground process, use the sleep time set by user input. In addition, I make use of the API to:

• Set the status message using the SET_STATUS procedure.
• Set the progress so far against a total workload using the SET_PROGRESS procedure.

In addition, for my application, I have created:

• A report based on APEX_APPL_PAGE_BG_PROC_STATUS to report on how my execution chain is progressing.
• A dynamic action to refresh my report periodically.
• An application process to check the progress of my execution chain using the GET_EXECUTION function, based on the Execution ID returned to: P0_EXECUTION_ID page item, which returns the current state.

• A Global Page Dynamic Action executed on page load to execute javascript to call the application process above (using apex.server.process) and generate a notification if the process completes successfully. So, the message should be displayed on the first load of an application page after the execution chain completes successfully while the user navigates the application.

As with my traditional foreground process, in the application, the user triggers the new Execution process by specifying a sleep time and clicking a button to submit the page.

This time, however, because the process is running in the background, the branch to another page executes immediately, not waiting for the process to complete. The target page is the report page displaying the progress of my process.

The report refreshes intermittently, showing the appropriate Status Message for that stage in the chain and progress against the total workload (see three screenshots below).

Refresh 1:

Refresh 2:

Refresh 3:

If the user tries to trigger four of these execution chains so that they are all running in the same session, this exceeds the permitted limit of three and an error message is displayed.

Then, as the user navigates around the application, on the first application page load after the process has completed successfully, a notification is displayed informing them of such.

Conclusion

The addition of the Execution Chain process to the APEX armour certainly simplifies navigating around the application while a background process is executed. With the provided supporting view and API, it becomes easier to track the progress of those processes and report on progress compared with scheduled jobs, as would have been required before APEX 23.1.

For more information, check out our APEX services, and if you liked this blog, check out our other APEX blogs here.

Remember to follow us on LinkedIn. We publish insight blogs on the latest technology developments every week.

Subscribe to Oracle APEX Insights if you want to stay tuned for more APEX updates.