Application Express Automations: No Code Background Processing

What Are APEX Automations

Introduced in APEX 21.1, Automations are a declarative way to run background processes without writing DBMS_SCHEDULER jobs. An Automation monitors data using a SQL query, and when rows are found, it executes a series of actions on each row. Think of it as a trigger that runs on a schedule, checking for conditions and acting on them automatically.

Creating an Automation

In Shared Components, navigate to Automations. Define its source query, schedule, and actions. For example, to automatically send reminder emails for overdue invoices:

-- Source Query
SELECT invoice_id,
       customer_email,
       invoice_number,
       amount_due,
       due_date
  FROM invoices
 WHERE due_date < SYSDATE
   AND status = 'UNPAID'
   AND (last_reminder_date IS NULL
        OR last_reminder_date < SYSDATE - 7);

Set the schedule to run daily. Add Action 1: Send Email using APEX_MAIL integration. Add Action 2: Execute PL/SQL to update the reminder date:

UPDATE invoices
   SET last_reminder_date = SYSDATE
 WHERE invoice_id = :INVOICE_ID;

Automation Types

APEX supports two automation types. Query automations run a SQL query and process each resulting row through the action chain. Scheduled automations simply run a PL/SQL block on a schedule without a source query. Query automations are more common because they combine data selection and processing in a single declarative definition.

Actions Available

Each automation can have multiple actions that execute in sequence: Execute PL/SQL Code, Send Email, Send Push Notification (for PWA apps), Execute Web Source Module (call a REST API), and Log a Message. You can chain these with conditional logic to build sophisticated workflows.

Monitoring and When to Choose Automations

APEX provides built-in logging showing a timeline of runs with status indicators. Use Automations when your background process is tied to your APEX application and benefits from declarative email and REST integration. Use DBMS_SCHEDULER when the job needs to run independently of APEX or when you need complex job chains.