Building Intelligent Workflows with Oracle APEX Automations

Introduction

In Oracle APEX 24.2, Automation has become a key feature for integrating event-driven workflows directly into your low-code applications. This post introduces what Automation is, when to use it, and how it simplifies routine tasks like sending emails, synchronizing data, or executing business logic, without needing a full PL/SQL background.

What is APEX Automation?

In Oracle APEX, Automation allows you to define background processes that react to data changes or scheduled intervals. These processes consist of a series of actions executed in order, typically based on query results. This makes it easy to monitor your data and trigger business logic, without writing complex infrastructure.

As a result, it’s useful to handle scenarios such as sending email notifications, processing approvals, or integrating external systems. Each automation is driven by a SQL query or a PL/SQL function and can run on a defined schedule or be triggered manually.

One of the powerful aspects of Automation is its support for multiple data sources, including:

The local database
REST Enabled SQL services
REST Data Sources

Key Capabilities of APEX Automations

Action Sequences

You can define one or more actions to run sequentially for each row returned by the automation query.
Most of the time, these actions are based on PL/SQL code. However, you can also use process plug-ins when needed.
In addition, you can control when each action runs by using row-level bind variables or by applying conditional logic.

Flexible Error Handling

Oracle APEX Automation offer built-in mechanisms to handle exceptions gracefully.
If an error occurs during execution, you have several options. For instance, you can choose to ignore the error, stop the automation, or automatically disable it to prevent further runs.

This flexibility helps ensure that your automation processes remain reliable and resilient.

Programmatic Execution

Need to trigger an automation from within your application logic? The APEX_AUTOMATION PL/SQL package lets you do just that.

Logging & Audit Trail

APEX keeps comprehensive records of each automation run, allowing access to execution results, error tracking, and modification history via the Automation Execution Log and History views.

About Execution Timing
Although automations are scheduled with precise intervals, they rely on a coordinator job in the APEX engine. As a result, actual execution typically begins a few minutes after the scheduled time. This behaviour ensures that APEX handles resource usage efficiently, especially in multi-tenant or shared environments.

When to use Automation

Background Processing of Business Rules

Auto-approve or reject requests based on criteria (e.g., leave requests, expense or holiday approvals).

Notifications

Send email alerts when thresholds are exceeded (e.g., low inventory, overdue tasks).

Data Monitoring and Cleanup

Automatically archive or purge old records.

Integration Triggers

Invoke REST APIs or background jobs when changes in data occur.

When to Prefer Automation over Alternatives

Prefer Automation over scheduled jobs when…	Avoid Automation when…
The logic must stay within the APEX application context.	The processing must occur outside the APEX schema or database.
You need simple, declarative control via the APEX builder.	Complex dependency management or transaction orchestration is required.
The process requires integration with APEX-managed notifications or audit logging.	You require high-frequency execution with sub-minute intervals. (APEX automation is scheduled via DBMS_SCHEDULER, typically at minute granularity).

How to create an Automation

To demonstrate how Automation works in Oracle APEX, we’ll walk through a practical use case that incorporates Artificial Intelligence.
Specifically, the goal is to automatically summarize user-submitted articles using an external AI service. After that, a notification email containing the summary will be sent to a reviewer.

This scenario illustrates how APEX’s native automation features, when combined with REST API integration, can support intelligent, event-driven workflows, all within a low-code environment.

Let’s get started with the implementation process.

Step 1: Prepare the Email Template

Define the structure

Navigate to Shared Components > Email Templates (User Interface section) and create a new template named: NEW_ARTICLE_SUMMARY

Use the following structure:

Identification

Template Name: New Article Summary
Static Identifier: NEW_ARTICLE_SUMMARY
Email Subject: Summary generated from AI service

Header

Copy to Clipboard

Body

Copy to Clipboard

Footer

Copy to Clipboard

The result would be something like this:

Email template

Save the template

Then, click on the “Create Email Template” button to save changes. Now, we would have our template created:

Email template created

Step 2: Define the AI REST Data Source

Function overview

To connect to an AI summarization service (e.g., OpenAI Platform), define a REST Web Source or call the API via PL/SQL using APEX_WEB_SERVICE. In the following example, we’ll use APEX_WEB_SERVICE:

Copy to Clipboard

-- HTTP headers
  apex_web_service.g_request_headers(1).name  := 'Content-Type';
  apex_web_service.g_request_headers(1).value := 'application/json';

apex_web_service.g_request_headers(2).name  := 'Authorization';
  apex_web_service.g_request_headers(2).value := 'Bearer <OUR_API_KEY>'; --> We must specify our API Key

-- REST call
  l_response := apex_web_service.make_rest_request(
    p_url         => 'https://api.openai.com/v1/chat/completions',
    p_http_method => 'POST',
    p_body        => l_request_body
  );

-- Parse JSON response
  apex_json.parse(l_response);
  l_result := apex_json.get_clob_val(p_path => 'choices[1].message.content');

return l_result;
end;

Security notes

The PL/SQL example above, which uses APEX_WEB_SERVICE, is provided as a template.
It has not been fully tested in a production environment.
You should review and validate the code in your setup.
Make sure to adjust the HTTP headers, endpoint URL, and authentication method to match your environment and security requirements.

Step 3: Create the Automation

Description of the Automation

This automation checks for new articles that haven’t been processed yet, so for each article found, APEX runs a PL/SQL block that:

Calls an external AI service to summarise the article content concisely.

Sends an email to a designated reviewer using a preconfigured APEX email template (already defined in Step 1), which includes the article title, author, and the AI-generated summary.
Updates the record in the database to mark it as processed, ensuring that it won’t be picked up again in future runs.

This automation runs on a schedule (e.g., every 15 minutes) and enables a lightweight but intelligent content review workflow by integrating Oracle APEX with AI via REST API.

Automation Setup in APEX

To create this automation in Oracle APEX, simply:

Select our application.
On the Application home page, click on Shared Components, and then the Shared Components page will appear.
Under Workflows and Automations, select Automations.

Click on the “Create” button and the “Create Automations Wizard will appear”:

Introduce the following information:

Automation setup: Scheduled every 15 minutes

Now, we specify the SQL query that will be the source of the data evaluated during the execution of the automation:

SQL Query Evaluated

Configure the Automation Actions.

Click the “Create” button. APEX will now display the automation details. From here, you can review and add the required actions.

Below the SQL query source of the data evaluated during the execution of the automation, there is a section called “Actions”, where we can set all the actions to run.

In our case, we will configure the automation with three sequential actions, executed for each row returned by the source query:

Call the AI summarization function we mentioned in Step 2 and store the generated summary in the column SUMMARY in the ARTICLES_SUBMITTED table.
Send an email to a reviewer using the pre-configured email template that includes the summary.
Mark the article as processed, so that it will not be picked up again in subsequent automation runs.

This structure allows Oracle APEX to handle each stage cleanly and declaratively while keeping the logic modular and easy to maintain.

Action 1: Generate AI Summary

Action Name: Generate AI Summary
Action Type: Execute Code
Location: Local Database
PL/SQL Code:

Copy to Clipboard

Here we have used the APEX_AUTOMATION package to write log information, which can be useful for debugging possible errors. Below we’ll deep into the APEX_AUTOMATION package.

Action 2: Send Summary Email

Action Name: Send Summary Email
Action Type: Send E-Mail
Send Email Settings:
- From: &APP_EMAIL.
- To: reviewer@automations_content.com
- Email Template: New Article Summary
- Set Placeholder Values:

This declarative action will automatically inject values from the current row into the email content.

Action 3: Mark the Article as Processed

Action Name: Mark Article as Processed
Action Type: Execute Code
Location: Local Database
PL/SQL Code:

Copy to Clipboard
Syntax Highlighterbegin
  update ARTICLES_SUBMITTED
     set processed_yn = 'Y'
   where id = :ID;

  apex_automation.log_info('Article Id already processed: ' || :ID);
exception
  when others then
     apex_automation.log_error('Error setting processed_yn, id: ' || :ID || ', error:' || SQLERRM);
end;

This ensures that each article is handled only once.

These would be the actions defined:

Once the actions of your automation have been defined and you’re ready to activate it, simply click the “Save and Run” button. The Automation will then be enabled and will run according to the frequency you’ve configured.

APEX_AUTOMATION package

Oracle offers the APEX_AUTOMATION PL/SQL package to manage automations programmatically, enabling actions such as activation, deactivation, and execution.

It can be invoked from any PL/SQL block running within the parsing schema of an APEX application, whether through tools like SQL Developer and SQLcl or within declarative PL/SQL processes inside Oracle APEX:

PL/SQL Processes Inside APEX

In Page Designer, a PL/SQL Code process can be defined to manually trigger automation in response to user actions or as part of a broader business workflow:

Copy to Clipboard
Syntax HighlighterBEGIN
   APEX_AUTOMATION.EXECUTE(p_static_id => 'my_automation_id');
END;

Scheduled Jobs or Stored Procedures

Call APEX_AUTOMATION from a stored procedure or a DBMS_SCHEDULER job, provided it runs under the same parsing schema.

External Tools (SQL Developer, SQLcl, etc.)

Allow direct execution of procedures by connecting to the application’s parsing schema:

Copy to Clipboard
Syntax HighlighterBEGIN
   APEX_AUTOMATION.ENABLE(p_static_id => 'data_cleanup');
END;

From a Database Trigger (Not Recommended)

While technically possible, calling EXECUTE from within a DML trigger is not recommended due to transactional context and concurrency limitations. Oracle does not guarantee stable behaviour for this use case.

Security and Privilege Considerations
The user executing the procedure must have the privileges required to run APEX_AUTOMATION, which is implicitly granted when operating from the parsing schema. No explicit GRANT is necessary when calling it from within APEX or its associated schema.

These are some of the most relevant procedures and functions in the APEX_AUTOMATION package:

PROCEDURES

ENABLE

Useful when you need to programmatically start an automation that has been disabled, such as re-enabling a process during a deployment or after maintenance:

Copy to Clipboard

DISABLE

Helps prevent further execution of automation, particularly useful during system changes, debugging, or temporary deactivation without deletion:

Copy to Clipboard
Syntax HighlighterAPEX_AUTOMATION.DISABLE (
    p_application_id    => 100, --> ID of the application which contains the automation
    p_static_id         => 'my_emp_table_automation' ) --> Static ID of the automation to disable

EXECUTE

Ideal for testing automation, executing it on-demand, or integrating it into another PL/SQL flow:

Copy to Clipboard
Syntax HighlighterDECLARE
    l_filters apex_exec.t_filters;
BEGIN
    apex_session.create_session( 100, 1, 'ADMIN' );

    apex_exec.add_filter(
        p_filters        => l_filters,
        p_column_name    => 'DEPTNO',
        p_filter_type    => apex_exec.c_filter_eq,
        p_value          => 10 );

    apex_automation.execute(
        p_static_id       => 'my_emp_table_automation',
        p_filters         => l_filters );
END;

This is an example of one of the different variants for running the EXECUTE procedure. If you want to deep on the rest of the variants, visit APEX_AUTOMATION.EXECUTE procedures.

LOG_INFO, LOG_WARN, LOG_ERROR

Oracle APEX provides built-in procedures within the APEX_AUTOMATION package that allow you to write custom messages to the Automation Execution Log. These procedures are:

APEX_AUTOMATION.LOG_INFO (p_message):
Logs an informational message. Use this for tracking normal operations, such as row processing or checkpoint status.
APEX_AUTOMATION.LOG_WARN (p_message):
Logs a warning message. Use this when an action is completed with unexpected results that do not halt the process.
APEX_AUTOMATION.LOG_ERROR (p_message):
Logs an error message. This does not stop the execution, but it will flag the row/action as an error in the execution log.

These logs are visible under App Builder > Shared Components > Automations > Execution Logs, providing valuable insight into the runtime behaviour of your automation.

FUNCTIONS

GET_LAST_RUN

Returns the ID of the most recent execution. It helps identify the latest run for reporting, audit, or programmatic inspection of execution metadata.

IS_RUNNING

It checks if a particular automation is currently running. Prevents overlapping or duplicate runs, or can be used as a condition to trigger alerts.

Conclusion

Oracle APEX Automations offer a robust and declarative way to build background processing into your applications without the complexity of external schedulers or procedural coding. Whether you’re sending notifications, integrating AI, or reacting to business events, Automations provide the tools to orchestrate these workflows in a reliable, maintainable way.

With support for SQL, PL/SQL, REST integrations, email delivery, and structured logging, APEX Automations are ideal for modern enterprise applications that require intelligent and reactive backend logic. Now is the perfect time to explore this feature and elevate your APEX solutions with smart automation.

If your organization needs support or is looking to optimize its processes, feel free to contact us at hello@novoshore.com. We are here to help you unlock the full potential of Oracle APEX Automations.