Oracle APEX - Seeing Execution Chain History

There are many excellent blogs already out there that explain what execution chains are and how to use them in Oracle APEX and I encourage those unfamiliar with the concept to read them. What I will say is that, in my opinion, their biggest advantage, and why most will use them, is the ability to run long processes in the background.

This means the user doesn't have to watch a loading icon for several minutes while a process runs, or worse, risk their session timing out. As a developer, you can get creative and offer a way to show the progress of the now asynchronous process to the user. There's a useful view called APEX_APPL_PAGE_BG_PROC_STATUS that shows how things are going.

When creating the execution chain in the page designer, if you turn on "Run in Background," you will see the following options:

To track progress, the most important option is "Return ID into Item." This allows you to get the unique EXECUTION_ID, which you will use to query APEX_APPL_PAGE_BG_PROC_STATUS. You can store it in a hidden page item on the page where the process is started, or you can choose to store it in a global page (0) item or an application item. This way, it remains accessible throughout the application, even if the user navigates away from the current page.

SELECT BG.PROCESS_NAME,
       BG.STATUS,
       BG.STATUS_MESSAGE,
       (BG.SOFAR / NULLIF(BG.TOTALWORK, 0)) * 100 AS PCT_COMPLETE,
       BG.CREATED_ON,
       BG.LAST_UPDATED_ON
FROM   APEX_APPL_PAGE_BG_PROC_STATUS BG
WHERE  BG.APPLICATION_ID = :APP_ID
AND    BG.PAGE_ID = :APP_PAGE_ID
AND    BG.EXECUTION_ID = :PXX_EXEC_ID;

You can use a query like the one above to show users the progress in a report that automatically refreshes every few seconds, or however you like to flex your creative muscles.

There is one important point to be made here, in fact it’s the entire reason for this article: this information isn’t persistent; it only lasts for the duration of the session. So how can you see the “history” of these execution chain processes? Well, the way these execution chains operate is by using DBMS_SCHEDULER jobs, so you have the usual DBA% views that keep this information for 30 days by default. You can change the retention period to something more long-term by asking your friendly, neighbourhood DBA to run:

-- Assuming the APEX background job runs as the DEFAULT_JOB_CLASS class.
BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE('DEFAULT_JOB_CLASS', 'LOG_HISTORY', 180);
END;

It is therefore advisable that you record the EXECUTION_ID somewhere in the backend database so you can reference this outside of the current APEX session and be able to use it to query DBA_SCHEDULER_JOB_RUN_DETAILS once it has completed. Here is an example of the log table you would write to whenever the execution chain is invoked through the UI:

Now for the important part: although undocumented, APEX currently creates its jobs with the naming convention of APEX$PROCESS_EXEC_nnn. where nnn is the EXECUTION_ID. You can use the following query to get the job’s run details once it has aged out of APEX_APPL_PAGE_BG_PROC_STATUS.

SELECT *
FROM   YOUR_LOG_TABLE C
JOIN   DBA_SCHEDULER_JOB_RUN_DETAILS RD
ON     RD.JOB_NAME = 'APEX$PROCESS_EXEC_' || TO_CHAR(C.BG_EXECUTION_ID)
WHERE  RD.OWNER = (SELECT TABLE_OWNER -- Get the current APEX Schema
                   FROM   ALL_SYNONYMS
                   WHERE  SYNONYM_NAME = 'WWV_FLOW'
                   AND    OWNER = 'PUBLIC')
AND    NOT EXISTS (SELECT 1
        FROM   APEX_APPL_PAGE_BG_PROC_STATUS BG
        WHERE  BG.EXECUTION_ID = C.BG_EXECUTION_ID);

As long as the naming convention of the jobs doesn't change in future versions of APEX, this method will let you track your execution chain processes beyond the time window that APEX_APPL_PAGE_BG_PROC_STATUS currently provides.