APEX_AUTOMATION in oracle APEX

APEX_AUTOMATION is a shared component feature that helps developers automate activities and tasks. It can trigger itself automatically at a specific time and event. These events could be yearly, monthly, hourly or weekly. These activities could be like sending emails, auditing tables processing orders etc. APEX_AUTOMATION can execute multiple processes sequentially.

Few sample activities

  • Processing Orders
  • Auto-approving requests
  • Sending email alerts

Steps to create the APEX_AUTOMTAION.

  • Go to Shared components
  • Go to Workflows and Automations
  • Click on Automations
  • Click on Create

Configurations

  • Name: Name of automation
  • Static ID: Static ID for this automation. The static ID is used when manually executing the automation with the APEX_AUTOMATION package (APEX_AUTOMATION.EXECUTE)
  • Type:
    • On-Demand: Can be executed on specific demand
    • Scheduled: Time event (Weekly, Daily, Hourly and Minutely)
  • Actions: List of actions which will be executed
  • Additional Code Execution:
    • Initialization Procedure: Provide the name of an Initialization Procedure, which is either defined as a PL/SQL object in the database schema, or in the above Executable Code. APEX will execute that procedure at the very beginning of an automation execution.
    • Before Row Processing Procedure: Provide the name of a Before Row Processing Procedure, which is either defined as a PL/SQL object in the database schema, or in the Executable Code. APEX will execute that procedure for each row, before it starts executing the defined actions.
      View Documentation
    • Cleanup Procedure: Provide the name of a Cleanup Procedure, which is either defined as a PL/SQL object in the database schema, or in the Executable Code. APEX will execute that procedure at the very end of the automation execution.

Let’s take the example code for action, it will send an email.

-- Example One: Plain Text only message
DECLARE
    l_body      CLOB;
    l_id NUMBER;
BEGIN
    l_body := 'Thank you for your interest in the APEX_MAIL 
package.'||utl_tcp.crlf||utl_tcp.crlf;
    l_body := l_body ||'  Sincerely,'||utl_tcp.crlf;
    l_body := l_body ||'  The Application Express Dev Team'||utl_tcp.crlf;
    l_id   := apex_mail.send(
        p_to       => 'some_user@somewhere.com',   -- change to your email address
        p_from     => 'some_sender@somewhere.com', -- change to a real senders email address
        p_body     => l_body,
        p_subj     => 'APEX_MAIL Package - Plain Text message');
END;
/

After running the automation we can see the email sent

APEX views to create the console history

  • APEX_APPL_AUTOMATIONS: Stores the meta data for automations of an application.
  • APEX_APPL_AUTOMATION_ACTIONS: Identifies actions associated with an automation APEX_APPLICATIONS
  • APEX_AUTOMATION_LOG : Log of automation executions. APEX_APPL_AUTOMATIONS
  • APEX_AUTOMATION_MSG_LOG: Messages of an automation execution.

Below reports can show the details about the automation, in the log we can see the successful Execution no.

Now let’s try Operating automation with API.

APEX_AUTOMATION package API has multiple procedures and functions to operate the automation.

  • DISABLE Procedure
  • ENABLE Procedure
  • EXECUTE Procedure
  • EXECUTE for Query Context Procedure
  • EXIT Procedure
  • GET_LAST_RUN Return Function
  • GET_LAST_RUN_TIMESTAMP Function
  • LOG_ERROR Procedure
  • LOG_INFO Procedure
  • LOG_WARN Procedure
  • RESCHEDULE Procedure
  • SKIP_CURRENT_ROW Procedure

Let’s have a sample table to test APEX_AUTOMATION functions and procedures. Create a table as TEST_AUTOMATION

Create table  test_automation
(
    ID  NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    CODE varchar2(100),
    exec_time date
);

Create New AUTOMATION to work on.

  • Name: ONTOOR_AUTOMATION
  • Static ID: ontoor-automation

Run and see if the AUTOMATION is working fine.

DECLARE
    l_filters apex_exec.t_filters;
BEGIN
    -- apex_session.create_session( 94553, 1, 'DEMO' ); -- If not executing from APEX
    apex_automation.execute(
        p_application_id  => 94553,
        p_static_id       => 'sample-automation',
        p_filters         => l_filters );
END;

AUTOMATION filters

Automation filters are used as Predicate on Automation query, It applies a filter to the automation query on the column selected. The below code applies a filter to the automation query on the DEPTNO column (DEPTNO = 10).

DECLARE
    l_filters apex_exec.t_filters;
BEGIN
    -- apex_session.create_session( 94553, 1, 'DEMO' ); -- If not executing from APEX

     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_application_id  => 94553,
        p_static_id       => 'sample-automation',
        p_filters         => l_filters );
END;

…… In progress 🙂