Stop Making Users Wait with APEX_AUTOMATION

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).

---

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.

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

3. 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'
);

• 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;

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;

---

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:

---

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

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):

       begin
           -- Simulate heavy work
           dbms_session.sleep(10); 
     
           insert into demo_job_log (payload, status, finished_at)
           values ('Async Task Triggered', 'SUCCESS', localtimestamp);
       end;
     

3. 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:

       begin
           apex_automation.execute(p_static_id => 'demo-async');
           apex_application.g_print_success_message := 'Automation triggered!';
       end;
     

4. 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

• APEX_AUTOMATION API Reference (Oracle Docs)
• Managing Automations in Oracle APEX (Oracle Docs)
• APEX Insights: Live Demo Application
• Oracle APEX 20.2 New Features: Automations

---

💖 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