I was architecting an APEX extension for an Oracle EBS customer where there was a need to call some standard APIs. These took a little while to run, and as the customer was using an APEX version prior to 23.1 we decided to submit these processes as concurrent programs. They could therefore execute asynchronously and control could be returned to the screen so the user did not need to wait and could progress with other operations. Of course there were ways of monitoring the status of these concurrent programs so if there was an issue then that could be addressed and the processing hadn’t just gone “into the ether”.
APEX 23.1 brought us Background Chain Processing through Background Page Processing which was further enhanced in APEX 23.2 with additions such as context sensitivity Really this is a wrapper around DBMS_SCHEDULER (which replaced good old DBMS_JOBS) and there’s a number of configuration parameters at workplace and application level to set the a limit to the maximum number of background page processing jobs executing. I first tried this on apex.oracle.com and I was just getting one job running at a time in a context and I suspect it may be due to this being (wisely) managed to restrict concurrency – but something to be aware of.
To demonstrate this new functionaility, let’s set up a chain of processes and run them in the background and monitor the progress. I am using APEX 23.2.6 and a 23c cloud database.
Create a simple interface
I firstly created a simple page to accept a context parameter and a button to execute the chain we will be creating.
The button does no more than show a message that the chain is been submitted and then submits the page which actually kicks off the chain.
Create an execution chain
We will create a chain with three steps so we can see the progress.
If we firstly look at the setup of the chain we will note some key items.
Type: Execution Chain. This identified the item as being of a new chain type.
Run In Background: We have toggled to “Yes” so that all processes in this chain will run asynchronously.
Return ID into Item: Here we can specify a page item which is set to the execution id, which can be used for monitoring. We are not going to set this.
Context Value Item: This is quite important as if we set serialize (later on the setup list) then executions with the same context value are batched up and scheduled for execution on a waitlist until the preceeding one has completed execution. That is the default, but you can also make them fail instantly so they are not held waiting to execute and the user is notified that an execution of the same type is already underway.
Serialize : If YES then executions of a chain with the same context value are not performed in parallel. We can set them to wait or not run at all (Show an error message).
When Already Running : Wait is the default, but can be set to Error. This means that if an execution with the same context is currently running, then this submission will error.
Create three steps in the chain
For Step1 we will insert a record into a table, just as an example, and then wait for 30 seconds so we have time to toggle to see the status of the process and watch it run. Note that by just setting the Execution Chain to the name of the chain we have linked this process as a step in the chain.
The execution sequence indicates the relative sequence of this step in the chain.
For Step 2 we do the same, but the message in the insert is different just for our information. The sequence number is also higher.
Now for Step 3, this time we do not need to wait and we can send an email (I have blanked out the email addresses) to indicate that the process has completed. If we were using a PWA a push would have been nice here perhaps.
Execute and check the results
We shall run this by setting a context of MarkContextTest and press the “Runchain” button.
We can see the progress by looking at the session state and choosing to look at the Background Executions. Note that we could also query the table APEX_APPL_PAGE_BG_PROC_STATUS
We see that the chain is running step 1
Now it changes to running Step 2
Finally it runs Step 3 and completes.
The “So far” and “Total work” are null as these have to be set by the developer who knows how far through the process is e.g. setting the Total Work to the number of records processed, etc. There is a good example in the documentation for this when we know we are going to do X of something so we can indicate where we currently are and how many we have to do.
BEGIN
for i in 1 .. 1000 loop
do_something( p_param => i );
apex_background_process.set_progress(
p_totalwork => 1000,
p_sofar => i );
END loop;
END;
Multiple Chain Executions for different contexts
If we run the chain twice in different contexts, by entering MARKA and pressing the “Runchain” button and then entering MARKB and pressing “Runchain” let’s see what we get.
We get two chains running in parallel, as we would hope.
Multiple Chain Executions for the same context
If we submit one for a context of MARKX and then press the “Runchain” button again to submit another executions for the same context MARKX, then if the Serialization is set to Yes and When Already Running is set to Error like this :
We get a message telling us that this background process is already running.
This is a really useful way of stopping someone submitting the processing multiple times.
If we set When Already Running is set to Wait and we try two submissions for context of MARKZ then the first one executes and the second one ( and any other subsequent ones ) will have a status of “Submitted for Execution” and wait until the prior one finishes.
This is a great new enhancement to APEX and exceptionally useful for when you want to kick off a process and run it in the background so that your users are not left waiting for jobs to run in the foreground. We have the flexibility to batch jobs up, control the number of jobs that can be run in parallel and also to stop the “same” job being submitted more than once too.