How to Create a Progress Bar for Long Processes in Oracle APEX

With the introduction of the "run in background" option within a process execution chain, we have significantly enhanced the efficiency and user experience of our application. This feature allows us to initiate long-running processes without keeping the application tied up, thus freeing it to handle other tasks simultaneously. In the past, users were required to wait until these lengthy processes were completed, which could be quite time-consuming and frustrating. Alternatively, we had to manually create custom jobs to shift these processes to the background, which was both complex and resource-intensive.

Now, with this new capability, we face the challenge of effectively communicating the status of these background processes to our users. To address this, we can implement a progress bar that reflects the current state of the background process. This progress bar provides users with real-time feedback, enhancing their interaction with the application by keeping them informed about the process's completion status.

Fortunately, we can create this progress bar using native APEX components, which simplifies the implementation process. This not only improves the overall functionality of our application but also significantly enhances the user experience by offering clear and immediate feedback on long-running operations. Below the simplified result of the build.

Creating background process

First, we create a execution chain and set it to run in the background. We also set the “Return ID into item” field with a hidden page item. In the example below the P100_BACKGROUND_ID is set.

Within the execution chain, we create a PL/SQL process that runs for two minutes. For now, we choose to loop 60 times, pausing for 2 seconds each time. We also update the function's progress using apex_background_process.set_progress(), where we can set the total work and how much has been completed so far. The package offers many options to retrieve or manipulate information about the running processes. Later, we'll query the table and use these values to calculate the percentage of work done.

begin
  for i in 1 .. 60 loop
    -- set timeout for 2 seconds
    dbms_session.sleep(2);

    -- update the background process on how far the process is
    apex_background_process.set_progress(
        p_totalwork  => 60
    ,   p_sofar      => i
    );
  end loop;
end;

Create progress bar

Now that the process is created we want to show the progress to the user. I’ve chosen to use a chart region. The chart type is Status meter gauge and orientation horizontal. We use the status meter gauge because this chart type allows the value to be converted into percentages. Also empty the maximum width to stretch the chart. The last attribute we set is the Automatic Refresh here we set the interval to 2 seconds, this will refresh the chart region every 2 seconds. This will give extra load to the page so when the process isn’t running we do not want to reload the chart every 2 seconds, by putting a server side condition based on the ID page item we can control the refreshing of the chart.

The value is set in the series attribute, where we enter the query shown below. We query the values we set in the process. The view apex_appl_page_bg_proc_status is available in every APEX (23.1+) instance.

select 
    totalwork 
,   nvl(sofar, 0) as sofar
,   0 as min_value
from 
    apex_appl_page_bg_proc_status 
where 
    application_id = :APP_ID
and execution_id = :P100_BACKGROUND_ID

The values need to be mapped to the chart like below. Then we’re al set to run the process and see the progress.

Clearing the progress bar

When the processing is complete, we want to clear the chart. By adding a few lines to the process, we can set the ID page item to null and use an on-change dynamic action to hide the chart region. Note: Keep in mind that the AJAX refresh on the chart continues even when the chart is hidden. If you want to stop this as well, you can submit the page instead, and the server-side condition will prevent the chart from rendering. However, this will reload the page and might be confusing for your users.

begin
  for i in 1 .. 60 loop
    -- set timeout for 2 seconds
    dbms_session.sleep(2);

    -- update the background process on how far the process is
    apex_background_process.set_progress(
        p_totalwork  => 60
    ,   p_sofar      => i
    );
  end loop;

  -- when complete wait 10 seconds then clear :P100_BACKGROUND_ID
  dbms_session.sleep(10);
  apex_util.set_session_state('P100_BACKGROUND_ID', null);
end;