19 Executing Repetitive Processing#

Use an Automation in Shared Components to define repetitive processing.

Official source: Executing Repetitive Processing

19.1 Understanding Key Automation Concepts#

An Automation lets you define a recurring job to run on a schedule or to invoke on demand.

Sequencing Automation Actions

Your automation performs a sequence of one or more actions. Each can execute code in the local database or a remote one. It can also send an email or push a notification to a user. When your app runs on Autonomous AI Database, server-side geocoding is available, too, as explained in Geocoding in the Background.

Running Actions Once, or Once per Row

The automation's action sequence can run a single time, or automatically repeat to process each row of a query you define. In the latter case, actions can reference the query column names as bind variables to reference values in the current row.

Processing Conditionally

You can configure a Server-side condition on both the automation itself, and any of its actions to perform work only under certain conditions.

Determining Frequency with Schedule Expression

If scheduled, a Schedule Expression indicates how often the job executes. For common situations, use the Interval Builder dialog. Otherwise, you can also enter any valid Database Scheduler syntax for more complex requirements.

Enabling or Disabling the Job as Needed

You control the automation's Schedule Status. If enabled, then the APEX engine runs the job on your schedule. If disabled, then it only executes if you run it interactively from App Builder's Automations list page or when you call APEX_AUTOMATION.EXECUTE.

Controlling Error Handling

By default the automation ignores errors that happen in an action, and the automation continues processing the following action and remaining rows. You can adjust this behavior to terminate the automation instead, and either keep it scheduled for its next run or disable it.

Consulting Automation Logs

The APEX engine maintains an automation log you can consult in App Builder, or query using the APEX_AUTOMATION_LOG view. Your automation can log additional information, warnings, and errors to the log using the LOG_INFO, LOG_WARN, or LOG_ERROR procedures in the APEX_AUTOMATION package.

Controlling Automations Programmatically

The APEX_AUTOMATION package has a procedure to EXECUTE an automation. When doing so, one signature offers a p_run_in_background boolean parameter to control whether it runs in the current user session (false) or a new session in the background (true). Other procedures let you RESCHEDULE, ENABLE, DISABLE, or TERMINATE an automation as well. In addition, your action code can call SKIP_CURRENT_ROW to immediately continue to the next row to process, or EXIT to end the automation prematurely without processing remaining rows.

Configuring Supporting Logic

In addition to the automation's action logic, you can optionally configure names of an Initialization Procedure, a Cleanup Procedure, and a Before Row Processing Procedure as required. These can be existing procedures, or defined as part of the automation in its Additional Code Execution section.

Working with Session State

Automations can read and write the values of application items as temporary storage. The automation's optional Initialization Procedure can assign an initial value to an application item, that the Before Row Processing Procedure as well as actions can reference and modify if needed.

In addition, if the automation processes the rows of a query, then actions can reference the current row's data using column names as bind variables. Where useful, actions can also update the current row's column values, but the APEX engine does not automatically save any such changes. Your action code needs appropriate logic to update any values in the database.

Executing with No Logged-in User

Unless you use APEX_AUTOMATION.EXECUTE to execute an automation in the current user session, they run in the background using a private background session with no logged-in user. In this situation, :APP_USER returns nobody.

Official source: Understanding Key Automation Concepts

19.2 Exploring an Automation Example#

Explore a Woods HR automation that gives one employee a random Saturday salary increase.

The Woods HR application uses an automation to randomly increase the salary of a lucky employee every Saturday.

Parent topic: Executing Repetitive Processing

Official source: Exploring an Automation Example

19.2.1 Winning Salary Increase Every Saturday#

Review the weekly salary increase rule that rewards one randomly selected lower-paid employee.

Woods Clinic management likes to reward lower income earners on the staff by randomly increasing the salary of one lucky employee by 3% every Saturday. If the weekly winner is salesperson, then they also gets a 3% increase in their commission. Employees love the program! The figure shows employee MARTIN's email client receiving an email from hr@example.com with the subject line You Won the Employee Salary Lottery! It contains the email text:

Hi MARTIN, Congratulations! You won the Employee Salary Lottery. Your salary is now 1288, and your commission is now 1442. The Woods Clinic Staff.

Figure 19-1 Martin Gets an Email That She's the Lucky Winner of a Raise

Official source: Winning Salary Increase Every Saturday

19.2.2 Identifying Automation Rows to Process#

Use an automation source query to choose lower-paid employees and prepare row values for actions.

The Saturday Employee Lottery automation identifies the rows to process using a source query. Since lottery candidates are lower-earning employees, it determines the salary cutoff for the bottom 25% of earners with the continuous percentile analytic function PERCENTILE_CONT. As shown below, the query's cutoff common table expression computes the salary limit for the group, then references that SALARY_LIMIT in the WHERE clause to retrieve employees earning that amount or less. It selects key columns from the EBA_DEMO_EMP table that automation actions use while processing each employee the query returns. The query aliases the ROWNUM pseudo-column to RN so each row gets an ordinal number.

Tip:

The query adds two extra NULL columns, aliased to NEW_SALARY and NEW_COMMISSION. Actions can assign values to these columns as temporary storage, passing data to later actions in the sequence. These values exist only in memory while the row is processed and are always VARCHAR2 type. 

with cutoff as (
  select percentile_cont(0.25)
         within group (order by sal) as salary_limit
  from   eba_demo_emp
)
select e.empno,
       e.ename,
       e.job,
       e.sal,
       e.comm,
       rownum as rn,
       nvl(:G_DEMO_EMAIL,'demo.user@example.com') as employee_email,
       null as new_salary,
       null as new_commission
from   eba_demo_emp e
join   cutoff c
  on   e.sal <= c.salary_limit

The figure shows the Source tab of the Automation edit page, showing the SQL statement above as the automation's data source.

Figure 19-2 Saturday Employee Lottery Automation Source Query

Official source: Identifying Automation Rows to Process

19.2.3 Configuring Supporting Automation Logic#

Use initialization code to prepare shared values before automation actions run.

The Saturday Employee Lottery automation uses an Initialization Procedure to pick the random number that identifies the weekly winner. As shown below, it defines the INIT_EMP_LOTTERY procedure in the Additional Code Execution section of the automation definition. It counts the low-earning employees using the same criteria as the source query. Then it assigns a random whole number between 1 and that count to the application item G_LOTTERY_PICK. Finally, it adds a message to the automation log.

Tip:

Because this procedure is defined inline in the automation, it can directly reference and assign application items as bind variables. If the Initialization Procedure were externally-defined, that would need to use SET_VALUE in the APEX_SESSION_STATE package instead and reference application items using V('ITEM_NAME'). 

procedure init_emp_lottery is
    l_cnt number;
begin
    dbms_random.seed(dbms_utility.get_hash_value(
                         rawtohex(sys_guid()), 0, 2147483647));
    select count(*)
      into l_cnt
      from   eba_demo_emp e
     where  e.sal <= (select percentile_cont(0.25)
                             within group (order by sal)
                      from eba_demo_emp);
    :G_LOTTERY_PICK := trunc(dbms_random.value(1, l_cnt + 1));
    apex_automation.log_info(
        apex_string.format('Picked lucky number for today is %s',
                           :G_LOTTERY_PICK));
end init_emp_lottery;

The figure shows the Additional Code Execution tab of the Automation edit page, with the PL/SQL source code mentioned above in the Executable PL/SQL Code text box and the Initialization Procedure set to the procedure name init_emp_lottery.

Figure 19-3 Additional Code Execution Section Defines an Inline Initialization Procedure

Official source: Configuring Supporting Automation Logic

19.2.4 Defining Automation Actions in Sequence#

Define automation actions that decide which rows to process, perform conditional updates, and notify affected users.

The Saturday Employee Lottery automation includes the sequence of five actions shown below:
  1. Log Employee Name Being Considered
  2. Give 3% Raise to Lucky Employee
  3. Bump Lucky Salesperson's Commission, Too
  4. Update Commission If Changed
  5. Email Lucky Recipient the Good News.

The figure shows the Actions tab of the Automation edit page showing the five actions described above, in execution sequence order, listing the Action Type for each one. Each one is explained in more detail below.

Figure 19-4 Five Actions in the Saturday Employee Lottery Automation

Log Employee Name Being Considered

Log Employee Name Being Considered writes a message to the automation log with information about the employee being processed. It is an Execute Code action with the following code. If the ordinal RN value matches the randomly selected winning row number, then it logs a message to that effect. Otherwise, it calls SKIP_CURRENT_ROW to continue on to process the next row. This avoids the execution of the remaining actions in the sequence for the non-winning employee row. 

if :RN = :G_LOTTERY_PICK then
    apex_automation.log_info(
        apex_string.format('Processing bottom quartile lucky employee %s with rn=%s',
                           :ENAME, :RN));
else
    apex_automation.skip_current_row(
        apex_string.format('Skipping bottom quartile employee %s with rn=%s',
                           :ENAME, :RN));
end if;

Give 3% Raise to Lucky Employee

If the employee is the lucky one, Give 3% Raise to Lucky Employee computes their 3% salary raise, updates their SAL in the EBA_DEMO_EMP table, and writes a message to the automation log. It is an Execute Code action with the following Server-side condition so it only executes for the winning employee. 

:RN = :G_LOTTERY_PICK

It has the following code. Notice how it's using the NEW_SALARY column in the current row as temporary storage for the new salary it calculates. The Send E-Mail action below passes this NEW_SALARY value to an email template placeholder. 

:NEW_SALARY := round(to_number(:SAL) * 1.03);
apex_automation.log_info(
    apex_string.format('Lucky employee %s gets 3% salary raise from %s to %s',
                       :ENAME, :SAL, :NEW_SALARY));
update eba_demo_emp
   set sal = :NEW_SALARY
 where empno = :EMPNO;
apex_automation.log_info(
    apex_string.format('Updated Lucky employee %s salary from %s to %s',
                       :ENAME, :SAL, :NEW_SALARY));

Bump Lucky Salesperson's Commission, Too

If the lucky employee is a salesperson, Bump Lucky Salesperson's Commission, Too computes their 3% commission raise and writes a message to the automation log. It is an Execute Code action with the following Server-side condition so it only executes for a winning salesperson with an existing commission. 

:RN = :G_LOTTERY_PICK and :JOB = 'SALESMAN' and :COMM is not null

It has the following code. Notice again how it's using the NEW_COMMISSION column in the current row as temporary storage for the new commission it computes. The Update Commission If Changed and the Send E-Mail action below both reference this value. 

:NEW_COMMISSION := round(to_number(:COMM) * 1.03);
apex_automation.log_info(
    apex_string.format('Lucky Salesperson %s also gets 3% commission bump from %s to %s',
                       :ENAME, :COMM, :NEW_COMMISSION));

Update Commission If Changed

If the commission has changed, Update Commission If Changed reflects the change in the database and writes a message to the automation log. It is an Execute Code action with the following Server-side condition to only run if a new commission got set. 

:NEW_COMMISSION is not null

It has the following code. Notice how it's referencing the NEW_COMMISSION column in the current row that the previous action might have assigned a value to. It shows how temporary storage columns can be assigned by earlier actions in preparation for a later action to save the changes. 

update eba_demo_emp
   set comm = :NEW_COMMISSION
 where empno = :EMPNO;
apex_automation.log_info(
    apex_string.format('Updated Lucky Salesperson %s commission from %s to %s',
    :ENAME, :COMM, :NEW_COMMISSION));

Email Lucky Recipient the Good News

If the employee is the lucky one, Email Lucky Recipient the Good News sends them an congratulatory email with their new salary, and commission if relevant. It is a Send E-Mail action with Server-side condition :RN = :G_LOTTERY_PICK configured as shown below. Notice how it's using the Email Salary Lottery Winner email template. The email template placeholders get their values using substitution syntax from the columns names in the current row.

Figure 19-5 Configuring Email Template and Placeholder Values for a Send E-Mail Action

Official source: Defining Automation Actions in Sequence

19.2.5 Establishing an Automation's Schedule#

Configure an automation schedule with the Interval Builder or Database Scheduler syntax.

For common scheduling requirements, the Interval Builder dialog is a quick way to configure your automation's Schedule Expression. For more complex requirements, enter any valid Database Scheduler syntax.

Parent topic: Exploring an Automation Example

Official source: Establishing an Automation's Schedule

19.2.6 Running an Automation on Demand#

Use the Automations list page to run one on demand.

As shown below, clicking the "play" button in the Run column of the report executes the job. If the automation's Schedule Status is Disabled, then the job runs a single time. If it's Active, then it runs and uses the Schedule Expression you configured to determine the Next Run as well. The figure shows the Automations list under Shared Components in App Builder. It highlights the "play" button to run the Saturday Employee Lottery automation.

Figure 19-8 Running an Automation from the List Page

Official source: Running an Automation on Demand

19.2.7 Viewing the Automation Log#

Use the Execution Log tab of the Automations list to review recent runs.

As shown below, click on the count in the Messages column to review log messages for that run. The figure shows the Execution Log tab of the Automations page under Shared Components. It lists recent automation runs, how many rows were processed, and provides a link to view automation messages written to the log during each run.

Figure 19-9 Viewing the Automation Execution Log List

In the Messages page, you can inspect them, sort them, and download them to any of the formats an Interactive Report supports. The figure shows the Messages page you visit when clicking on a particular automation run. Logged messages display in an Interactive Report.

Figure 19-10 Inspecting Automation Execution Log Messages

Official source: Viewing the Automation Log

19.2.5.1 Using Interval Builder to Schedule Job#

Use the Interval Builder to create an automation schedule expression.

Your automation's Schedule Expression uses a compact syntax to establish when the job runs and how often. Use the Interval Builder dialog to configure the most common use cases. For example, as shown below, choosing the following options:
  • Frequency – Weekly
  • Interval – 1
  • Execution Day – Sat
  • Execution Hour – 20
  • Execution Minute – 50

yields an expression that means "Weekly on Saturday at 20:50":

FREQ=WEEKLY;INTERVAL=1;BYDAY=SAT;BYHOUR=20;BYMINUTE=50

The figure shows the Interval Builder dialog you can open using the button in the Automation edit page containing a wrench and a clock.

Figure 19-6 Configuring the Automation Scheduler Expression with Interval Builder

Official source: Using Interval Builder to Schedule Job

19.2.5.2 Using More Complex Schedule Expressions#

Use Database Scheduler syntax for advanced automation schedules and test future run dates with PL/SQL.

The automation's Schedule Expression supports any Database Scheduler syntax. These are the same ones the DBMS_SCHEDULER package accepts to create named schedules and create scheduled jobs. The diagram below summarizes the options you can use. You specify an expression using semicolon-separated list of one or more KEYWORD=VALUE elements. Supported keywords include:
  • FREQ – Frequency of repetition (required)
  • INTERVAL – Repetition counter (n = repeat every nth frequency unit)
  • <Time Filter> – Identifies specific time elements to fine-tune exactly when it repeats
  • <Set Operations> – Combine with a named schedule, or pick occurrences within a cycle
  • <Period Limiting> – Limit the total number of cycles, or pick specific cycle occurrences.
Figure 19-7 Syntax for DBMS_SCHEDULER Calendar Expressions

Use the following PL/SQL block in the SQL Commands page to test your scheduler expression. It helps you validate that your candidate syntax gives the expected results. Call next_five_dates with any expression you want to experiment with. 

declare
    procedure next_five_dates(
        p_calendar_string in varchar2)
    is
        l_next_date timestamp with time zone;
        l_return_date_after timestamp with time zone := systimestamp;
        l_start_date timestamp with time zone := systimestamp;
    begin
        dbms_output.put_line(p_calendar_string);
        for i in 1..5 loop
            dbms_scheduler.evaluate_calendar_string(
                calendar_string   => p_calendar_string,
                start_date        => l_start_date,
                return_date_after => l_return_date_after,
                next_run_date     => l_next_date);
            dbms_output.put_line('Next ' || i || ': ' ||
                                to_char(l_next_date,
                                        'DY DD-MON-YYYY HH24:MI:SS TZR'));
            l_return_date_after := l_next_date;
        end loop;
    end;
begin
    -- "Every week on Saturday at 20:50"
    next_five_dates('FREQ=WEEKLY;INTERVAL=1;BYDAY=SAT;BYHOUR=20;BYMINUTE=50');
    -- "Last Saturday of the month at 20:50"
    next_five_dates('FREQ=MONTHLY;INTERVAL=1;BYDAY=-1 SAT;BYHOUR=20;BYMINUTE=50');
end;
Running the example above produces output like the following:
FREQ=WEEKLY;INTERVAL=1;BYDAY=SAT;BYHOUR=20;BYMINUTE=50
Next 1: SAT 04-OCT-2025 20:50:16 +00:00
Next 2: SAT 11-OCT-2025 20:50:16 +00:00
Next 3: SAT 18-OCT-2025 20:50:16 +00:00
Next 4: SAT 25-OCT-2025 20:50:16 +00:00
Next 5: SAT 01-NOV-2025 20:50:16 +00:00
FREQ=MONTHLY;INTERVAL=1;BYDAY=-1 SAT;BYHOUR=20;BYMINUTE=50
Next 1: SAT 25-OCT-2025 20:50:16 +00:00
Next 2: SAT 29-NOV-2025 20:50:16 +00:00
Next 3: SAT 27-DEC-2025 20:50:16 +00:00
Next 4: SAT 31-JAN-2026 20:50:16 +00:00
Next 5: SAT 28-FEB-2026 20:50:16 +00:00

Official source: Using More Complex Schedule Expressions