Introduction

Last week, apex.oracle.com has been upgraded to Oracle APEX 23.1 letting us play with the new features built with ❤ by the APEX team. One of them is new Execution Chain process type that allows us to group page processes and choose between foreground or background execution. It's a pretty interesting feature as it allows us to easily switch long processes to run in the background and monitor the execution using the new built-in apex_page_bg_proc_status view and apex_background_process package.

How to use this new feature?

Creating an Execution Chain process

Like any other type of process, you can add it from the page by right-clicking, creating a process, choosing Run Chain as the type and giving it a name. It looks like you can nest it by associating one runtime chain with another. I haven't tested this yet.

You can now add one or more processes to this Execution Chain.

By default, the execution chain is executed in the foreground but, and this is the magic, you can enable the "Run in background" setting

You can define 4 additional attributes to control how APEX will handle it in the background:

Reporting the execution status and progress

Executing the chain in the background is easy but how do I report the progress and the status of the execution?

The new apex_background_process API contains two procedures to do it: set_progress and set_status

In the above example, I created 3 processes that do nothing but wait in a loop and update the status and the progress of the execution. Here is the code of one of them

begin
    apex_background_process.set_status(
        p_message => 'Analysing file (1/3)'
    );
    for i in 1..10
    loop
        dbms_session.sleep(1);
        apex_background_process.set_progress(
            p_totalwork => 30,
            p_sofar => i
        );
    end loop;
end;

Of course, in a real-world example, you would not just call dbms_session.sleep(1); .

Displaying the execution status and progress

This is great but what I want to be able to achieve is displaying this information to my end users. So they know that we are doing something in the background and they probably have to wait for it.

To achieve this, you can use the apex_appl_page_bg_proc_status view which contains all the background processes execution information.

select execution_id,
       process_id,
       process_name,
       current_process_name,
       status,
       status_code,
       status_message,
       coalesce(sofar, 0) as sofar,
       coalesce(totalwork, 100) as totalwork,
       coalesce(sofar, 0) / coalesce(totalwork, 30) * 100 progress
  from apex_appl_page_bg_proc_status
 where application_id = :app_id
   and page_id = :app_page_id
   and session_id = :app_session

Now if you use this query with a simple classic report and update the progress column to display a percent graph, you get an Execution Chain Monitor

Pretty nice, isn't it?

Aborting background execution

Sometimes, you do want to stop background tasks and the good news is that there is an API for that: apex_background_process.abort

You can decide to abort all the process executions or only one specific execution

begin
    -- Abort all the execution of the process
    apex_background_process.abort(
        p_process_id => XXX
    );

    -- Abort only the specified execution of the process
    apex_background_process.abort(
        p_execution_id => XXX
    );
end;

Adding the abort execution to the report

Create a new column and in the HTML expression add this code

{case STATUS_CODE/}
    {when SUCCESS/}
    {when ABORTED/}
    {otherwise/}
        <button 
            type="button" 
            onclick="apex.event.trigger(document, 'abort-execution', '#EXECUTION_ID#')" 
            class="t-Button t-Button--icon t-Button--danger t-Button--simple t-Button--iconLeft">
    <span aria-hidden="true" class="t-Icon t-Icon--left fa fa-ban"></span>Abort Execution
</button>
{endcase/}

Then create a dynamic action to react to the abort-execution custom event

Add a first action type of Set Value to get the execution ID and store it into a page item

Add another action of type Execute Server-side Code to abort the execution

begin
    apex_background_process.abort(p_execution_id => :P23_EXECUTION_ID);
end;

Finally, add a refresh action to refresh the report

Conclusion

This feature is a pretty exciting one because it allows us to easily execute long-running processes in the background in a low-code way. Simply switch on the attribute and use the API to report the status and progress of the execution. This was possible before but requires a lot of manual coding.

One more APEX feature that lets us focus on solving business problems instead of writing custom code! Thanks, Oracle APEX team

Demo available here: https://apex.oracle.com/pls/apex/r/louis/examples/background-processes