APEX Background Process Progress Bar

When building pages in Oracle APEX, we often need to execute processes that can take some time to complete. A static loading screen with no feedback can create a poor user experience, leaving users uncertain about what’s happening.

Fortunately, Oracle APEX offers the Run in Background feature for Processes Execution Chains, allowing long-running tasks to execute seamlessly. However, this introduces a new challenge: how do we keep users informed about the progress of a background process?

In this post, I’ll show you a simple and effective way to provide real-time progress updates to your users, enhancing both usability and experience.

Setup

Process PL/SQL Source

Your process will run a PL/SQL code where you have a total amount of work to process. By tracking both the current progress and the total workload, you can report the execution progress back to the Oracle APEX engine using the APEX_BACKGROUND_PROCESS.SET_PROGRESS procedure as follows:

BEGIN
    -- Example of a loop that simulates a long-running process
    FOR i IN 1..60 LOOP

        -- Simulate a time-consuming operation with a delay using dbms_session.sleep()
        -- In a real scenario, this could represent a task that takes time, 
        -- such as processing records or complex calculations.
        dbms_session.sleep(1);

        -- Report the progress of the background process to APEX
        apex_background_process.set_progress(
            p_totalwork => 60, -- Total amount of work to be completed
            p_sofar     => i   -- Current progress (incremented in each iteration)
        );
    END LOOP;
END;

What’s Happening Here?

APEX_BACKGROUND_PROCESS.SET_PROGRESS updates the sofar attribute in real time, reflecting the current progress of your background process. By updating this value at each iteration, you can query the progress and display it on the page in any way you like (a progress bar, status message or logs).

Creating the Progress Bar

Create a Chart Region
Chart Attributes
- Chart
  - Type: Status Meter Gauge
- Layout
  - Maximum Width: Empty
  - Height: 30
- Gauge
  - Orientation: Horizontal
  - Show Plot Area: On
- Value
  - Value Type: Percent
- Automatic Refresh
  - Interval: Set the refresh interval to your desired frequency to keep the chart updated in real time. I'll use 1 second for this example.

Series Source
- To query the background process progress set with the procedure inside our code we can use the APEX_APPL_PAGE_BG_PROC_STATUS view.
- Source
  - Type: SQL Query
  - Page Items to Submit: PX_EXECUTION_ID

-- Query example:
SELECT 
    NVL(totalwork, 100)   AS max, 
    NVL(sofar, 0)         AS value, 
    0                     AS min 
FROM 
    apex_appl_page_bg_proc_status 
WHERE
    -- Filter by the current application 
    application_id = :app_id 
    -- Uses the newly created page item, tying to the specific background process
    AND execution_id = :PX_EXECUTION_ID

Map the columns
- Value: VALUE
- Minimum Value: MIN
- Maximum Value: MAX

And that’s it! 🎉 You now have a fully functional progress bar that provides real-time feedback to your users as the background process runs.

Making it look better

The progress bar we’ve built so far demonstrates the core functionality and can be adapted to fit your needs. However, I'll give you some simple tips to improve its appearance and make it more intuitive.

Showing the Progress Bar dynamically

Create a Hidden page item
- Create a Hidden page item to control the display of the progress bar (PX_SHOW_PROGRESS_BAR)
- Set the item's Default Static Value as "N“
Add a Server-side Condition to the Progress Bar region
- Type: Item = Value
- Item: PX_SHOW_PROGRESS_BAR
- Value = S

Create a Page Process
- Create a Page Process to change the Progress Bar region display control item value

    BEGIN
        -- Set the new item's session state value as 'S' so it will be shown.
        :PX_SHOW_PROGRESS_BAR := 'S';
    END;

Under Server-side condition, on When Button Pressed, select the same button used on the main Execution Chain.

Better Look

On the chart creation I gave you some example values, but you can customize its orientation, width, and height based on your preferences. You may also find the template options helpful for additional adjustments. Within the chart series attributes, under the Appearance tab, you can modify the main and plot area colors to align with your design needs. Additionally, under Thresholds, you can set distinct colors to effectively highlight specific data ranges.

Attention

Since the values of the execution ID and chart region’s display control items are set in session state, it’s important to implement a Clear Cache/Reset when navigating to this page—whether through the menu, a redirect, or any other method. This ensures the user won’t encounter issues caused by incorrect session state values.

Beyond the Progress Bar

If you want more flexibility beyond simply displaying a progress bar, Oracle APEX provides additional tools to access and manipulate the execution status of a background process.

Instead of relying solely on the apex_appl_page_bg_proc_status view, you can use the APEX_BACKGROUND_PROCESS.GET_EXECUTION function. This function returns a t_execution record datatype containing the entire status of the specific execution ID.

-- Function example
DECLARE 
    l_execution apex_background_process.t_execution; 
BEGIN 
    l_execution := apex_background_process.get_execution( 
                       p_application_id => your_app_id, 
                       p_execution_id => your_execution_id 
                    );

    dbms_output.put_line( 'Execution State: ' || l_execution.state ); 
END;

=> Execution State: EXECUTING

‼️ I also recommend taking a full look at the APEX_BACKGROUND_PROCESS package. ‼️
This package provides a wide range of functionalities to manipulate and extract information about background processes running in your Oracle APEX application.

Conclusion

Creating features like a progress bar in Oracle APEX shows how powerful and flexible the platform is. Often, APEX already provides built-in tools and options to solve challenges that developers might overcomplicate elsewhere. This is the essence of low-code → focusing on efficiency and simplicity without sacrificing functionality. I hope you enjoyed this guide and feel inspired to explore more ways to leverage the built-in capabilities of APEX.

Thank you for reading! 🙏🚀