APEX Automations

As part of the release of Application Express 20.2, Oracle announced the inclusion of APEX Automations. But what are they? And are they any good?

Essentially, APEX Automations are a mechanism for using the Oracle database job scheduler but using the declarative features of APEX. Automations employ the new APEX_AUTOMATION package that is built on top of DBMS_SCHEDULER.

The APEX API Reference introduces automations as “… a sequential set of actions which are triggered by query results. Use automations to monitor data and then perform the appropriate action, such as auto-approving specific requests and sending email alerts.”

Sounds promising - a tool for introducing scheduled processes that carry out actions based on selected data, without needing to create the jobs and schedules using the DBMS_SCHEDULER directly – especially useful if you haven’t the privileges to access DBMS_SCHEDULER.

Configuring Automations

Automations are configured within APEX App Builder as Shared Components.

Shared Components > Application Logic > Automations

In order to configure a working automation there are essentially three groups of components:

• Settings (including name and scheduling details)
• Source (determines the conditions under which action will be taken)
• Actions (actions taken if the Source criteria is met)

Settings

Having given your automation a name you will need to decide when you want it to run, configured in the Settings region

If you select On Demand, the automation will need to be executed programmatically using the APEX_AUTOMATIONS package.

Many automations will be Scheduled, in which case the Schedule Expression needs to be provided. This is the same format as used by DBMS_SCHEDULER, but for the uninitiated, there is the Interval Builder that does this declaratively.

Scheduled automations will not execute unless ‘Schedule Status’ is ‘Active’.

The ‘Actions Initiated On’ item lets you decide how you determine when the Action processes will be executed. If set to ‘Always’ the actions will always be executed according to the schedule. ‘Query’ and ‘Function Body Returning Boolean’ provides a means of setting constraints on when the Action processes are executed.

So, in my example (screenshots above), I have chosen to run a scheduled automation every weekday at 06:30.

Source

If in ‘Settings’ the ‘Actions Initiated On’ item is set to ‘Query’ or ‘Function Body Returning Boolean’ then source code will be required to determine when the Actions are carried out. So, in a ‘Query’ or ‘Function Body Returning Boolean’ controlled automation, the Source will always be executed but the subsequent Actions may not be if the Source criteria are not met.

There are quite a few options available for providing Source code, outlined in the table below.

For example, if I configure:

The Source entry will be an SQL Query that does or doesn’t return rows. What happens when the Source query is executed is determined by the Actions configuration.

So, in my example, I have a simple query that is selecting employee details where the employee has a hire date in the past, but whose status is ‘0’ (Not Started). This is an anomaly so needs to be investigated and action taken to either register that these employees enrolled or that they never started.

Actions

Multiple actions can be configured to be executed. The table above indicates how and when Actions are executed, depending on the results returned by the Source i.e. when rows are returned, when no rows are returned or when True is returned.

For ‘Function Body Returning Boolean’ sources, the Actions will only be performed if True. But for ‘Query’ sources, there is the option to choose between ‘Rows Returned’ and ‘No Rows Returned’.

When creating an Action, again there is a choice between executing Local Database PL/SQL Code or a REST Enabled SQL Service. Additional server-side conditions can also be specified if required (not in the screenshot below).

So, in my example, a single Action will be performed on each row if rows are returned by the Source SQL. Keeping things very simple this is just an update on the employee record to say that some kind of action is required to sort this employee. In the real world, it might be desirable to send an email notification or invoke some other process flow.

What is nice about the processing of each row (in the SQL Query returning rows scenario) is that the selected columns from the Source output act as input parameters into the Action process - ’employee_id’ in my example above.

Once Automations are set up, they are displayed in the Automations report, providing headline information about them and the option to execute ad-hoc.

APEX_AUTOMATIONS API

In addition to configuration pages within APEX, there is also the APEX_AUTOMATIONS package which provides functions and procedures to manage automations programmatically e.g. enable/disable, execute, get last run details, logging, reschedule.

Under the Hood

Looking under the hood a little, the Oracle database instance includes a job called ORACLE_APEX_AUTOMATIONS (owner APEX_200200 user) the purpose of which is to execute automations that are due. So, the Automations that you configure your APEX application do not in themselves insert jobs into the DBMS_SCHEDULER_JOBS table. But completed executions of the automations do appear on the DBMS_SCHEDULER_JOB_RUN_DETAILS table (with job name ‘APEX$AUTOMATION_<ID>’ where <ID> is the automation ID.

APEX Automations configuration meta-data can be found in the APEX view APEX_APPL_AUTOMATIONS. This includes details of the Table/SQL/PLSQL source that initiates the action. The APEX_APPL_AUTOMATION_ACTIONS view is home to the meta-data for the actions triggered.

Summary

Automations provide a useful, declarative way to schedule processes for your APEX application, particularly if, as a developer, you don’t have access privileges to manage this directly using the DBMS_SCHEDULER package. They provide a facility to automate functionality (e.g. approvals, email notifications) triggered simply by monitoring application data.

Author: Philip Ratcliffe

Job Title: Oracle APEX Development Consultant

Bio: Philip is a Development Consultant at DSP-Explorer. Building on considerable experience in development including using Oracle PL/SQL and supporting Oracle EBS, Philip is employing APEX to provide quality, bespoke software solutions to a range of organisations.