DEV Community

Cover image for Stop Making Users Wait with APEX_AUTOMATION
Vinny Jiménez
Vinny Jiménez

Posted on

Stop Making Users Wait with APEX_AUTOMATION

#orclapex #architecture #plsql #performance

Read this APEX Insights in Spanish.

We have all been there: a user clicks "Submit," and the browser's loading spinner
becomes their only companion for the next 20 seconds. Whether it’s generating a
50-page PDF, synchronizing data with an external ERP via REST, or performing
complex batch calculations, blocking the user session for long-running tasks is
an Architectural Anti-pattern.

In modern web development, users expect high-performance, non-blocking
interfaces. In the world of Oracle APEX, that means mastering
APEX_AUTOMATION.

Introduced as a native component in version 20.2, APEX_AUTOMATION provided a
professional wrapper around the classic DBMS_SCHEDULER, tailored specifically
for the APEX lifecycle. It’s not just a "job runner"; it’s an orchestration
engine.

The Architectural Pivot: Blocking vs. Non-blocking

The fundamental shift a Senior Architect makes is moving from "Doing it now" to
"Handling it eventually."

When a process runs in the foreground (the user session), it consumes one of
your precious ORDS connections and forces the user to wait. If that connection
times out, the user doesn't know if the process finished, failed, or is still
"zombie-running" in the background.

By offloading to APEX_AUTOMATION, you decouple the Intent (the user wanting
to run a task) from the Execution (the database running it).

Mermaid Diagram

The Engine: Understanding Automations

Native Automations are defined declaratively in Shared Components >
Automations. They consist of:

  1. Trigger Type:
* **Scheduled**: Runs on a cron-like schedule (for example, "Every hour").

* **On Demand**: Only runs when explicitly called via API.
Enter fullscreen mode Exit fullscreen mode

  1. Source: A SQL Query or PL/SQL Function that identifies what needs to
    be processed.

  2. Actions: The PL/SQL blocks that execute once for each row in the source
    (or once globally).

Patterns: Poll vs. Push

There are two primary ways to design your background architecture:

1. The Polling Pattern (Scheduled)

The automation runs every 5 minutes, queries a "Queue Table" (for example,
SELECT * FROM task_queue WHERE status = 'PENDING'), and processes any new
items.

  • Best for: Decoupled systems where multiple processes feed into a single background engine.

2. The Push Pattern (Immediate Async)

You define an "On Demand" automation. When the user clicks a button, instead
of running the code, you call:

apex_automation.execute(
    p_static_id => 'HEAVY_DATA_PROCESS'
);
Enter fullscreen mode Exit fullscreen mode
  • Best for: Direct UX responsiveness where you want the job to start now, but without making the user wait.

Implementation: Setting Up for Success

The Naive Approach (Foreground Processing) 

-- ❌ DANGEROUS: Blocks user session, risks timeouts
BEGIN
    -- Heavy Logic (for example, 30 seconds)
    heavy_processing_pkg.run_data_sync;

    apex_application.g_print_success_message := 'Sync complete!';
END;
Enter fullscreen mode Exit fullscreen mode

The Consultant's Approach (APEX_AUTOMATION)

First, define your automation in Shared Components with Static ID SYNC_ENGINE.
Then, trigger it safely:

-- ✅ SAFE: Async execution, immediate return
BEGIN
    -- We can pass context via session state or a custom queue table
    insert into app_job_queue (task_type, payload) 
    values ('DATA_SYNC', :P10_PAYLOAD);

    -- Trigger the automation to check the queue
    apex_automation.execute(p_static_id => 'SYNC_ENGINE');

    apex_application.g_print_success_message := 'Sync started in the background.'
                                                || ' Check the log for status.';
END;
Enter fullscreen mode Exit fullscreen mode

Monitoring: The Architect's Dashboard

A background job is only as good as its visibility. APEX_AUTOMATION provides
built-in logging views that are essential for maintenance.

  • APEX_AUTOMATION_MSG_LOG: Detailed messages and errors.

  • APEX_AUTOMATION_LOG: Overall execution history (Success/Failure/Duration).

A Senior Architect builds an internal "Admin Dashboard" to monitor these
views, ensuring that if a job fails at 3:00 AM, it is caught and corrected
before the business starts their day.

Best Practices for Background Excellence

  1. Idempotency: Ensure that if a job runs twice (for example, after a
    retry), it doesn't duplicate work. Use unique keys or status checks.

  2. Bind Variables: Even in background sessions, use bind variables to
    prevent SQL injection and allow for plan stability.

  3. Error Handling: Wrap your automation actions in a BEGIN...EXCEPTION
    block to log specific application errors to a custom table or the APEX log.

  4. Transaction Management: Remember that each automation run is its own
    database session. COMMIT logic should be handled carefully within the
    PL/SQL actions.

Idempotency Example

To ensure your background job is safe to retry, use a check like this in your
automation action:

Mermaid Diagram

Live Demo: See it in Action

To truly appreciate the power of APEX_AUTOMATION on background processing,
you should see it in action. Since we are using an "Immediate Async" pattern,
the goal is to show the user a success message instantly while the database
works for another several seconds.

See it in Action

APEX_AUTOMATION Async Demo

Build Instructions

If you want to see this in action before building it, check out our
Live Demo.

If you have a workspace on apex.oracle.com, follow
these steps to build the demo:

  1. Create a Log Table:

    create table demo_job_log (
    id          number generated always as identity primary key,
    payload     varchar2(100),
    status      varchar2(20),
    created_at  timestamp default localtimestamp,
    finished_at timestamp
);

  2. Define the Automation:

* Go to **Shared Components > Automations**.

* **Name**: `Demo_Heavy_Process`, **Static ID**: `demo-async`.

* **Trigger**: `On Demand`.

* **Action (PL/SQL)**:
Enter fullscreen mode Exit fullscreen mode 
    ```sql
    begin
        -- Simulate heavy work
        dbms_session.sleep(10); 

        insert into demo_job_log (payload, status, finished_at)
        values ('Async Task Triggered', 'SUCCESS', localtimestamp);
    end;
    ```
Enter fullscreen mode Exit fullscreen mode
  1. Create the Trigger Page:
* Create a new Blank Page.

* Add a **Button** (e.g., `START_PROCESS`).

* Add a **Page Process** (Processing tab) that runs when the button is clicked:
Enter fullscreen mode Exit fullscreen mode 
    ```sql
    begin
        apex_automation.execute(p_static_id => 'demo-async');
        apex_application.g_print_success_message := 'Automation triggered!';
    end;
    ```
Enter fullscreen mode Exit fullscreen mode
  1. Verification: Click the button. Notice the page reloads instantly. If you check the demo_job_log table after 10 seconds, the row will appear.

Conclusion

Mastering APEX_AUTOMATION is the hallmark of a Senior Oracle APEX
Architect. It moves your applications from simple "CRUD" tools to
enterprise-grade systems capable of handling complex, long-running operations
with elegance and reliability.

Stop making your users wait. Start automating.

Question for the community: How are you currently handling long-running
processes in your APEX apps? Are you still using the naive approach, or have
you already made the jump to APEX_AUTOMATION? Let's discuss in the comments!

🚀 Take the Next Step

  1. Live Demo: Check out the APEX Insights demo of the
    "Immediate Async" pattern.

  2. Review your app: Identify any process taking more than 2 seconds and
    consider moving it to an Automation.

  3. Subscribe to APEX Insights: Get advanced architectural tips delivered
    straight to your inbox.

  4. Share the Knowledge: Connect with me on LinkedIn to discuss your
    background processing challenges!

☕ Schedule a Call |
💼 Connect on LinkedIn |
🐦 Follow on X

References

💖 Support My Work

If you found this APEX Insights helpful, consider supporting the
open-source efforts of the APEX community!

GitHub Sponsors |
Buy Me a Coffee

Top comments (0)