Creating an Automation

17.3.2 Creating an Automation

Create an automation as a Shared Component on the Automations page.

The following example demonstrates how to create an automation that runs every two hours. This automation increases the salary (SAL) of salesmen. For each salesman (SALESMAN) with a commission (COMM) greater than 500, the salary (SAL) increases by 1.5%. For all other salesman, the salary (SAL) increases by 1%.

Tip:

The examples in this section are built on the EMP table available in the sample dataset, EMP / DEPT. To install a sample dataset, see "Using Sample Datasets" in Oracle Application Express SQL Workshop Guide.

To create an automation:

Create a new application.
Navigate to the Automations page:
1. On the Workspace home page, click App Builder.
2. Select the new application.
3. On the Application home page, click Shared Components.
  The Shared Components page appears.
4. Under Application Logic, select Automations.
The Automations page appears.
To create an automation, click Create.

The Create Automations Wizard appears.
Specify the automation name and define the execution schedule:
1. Name - Enter a name for the new Automation. For example, Increase SAL.
2. Type - Select an automation type. Select Scheduled.
  
  Scheduled executes automatically based on the selected frequency. On Demand executes when called explicitly using the APEX_AUTOMATION package.
3. Actions initiated on - Select Query.
  
  This option determines whether the execution of Automation Actions should be initiated based on a Query (that is, the rows returned can then be used by the actions), or if the actions should Always be executed..
4. Execution Schedule - Select an execution schedule for the new automation. Select Custom.
  The Frequency, Interval, Execution Minute attributes only display if Execution Schedule is set to Custom.
5. Frequency - Specify whether the execution schedule should be based on a daily, hourly or minutely basis. Select Hourly.
6. Interval - Enter 2 so that automation executes every 2 hours.
7. Execution Minute - Specify the execution minute, from 0 to 59. Leave this to the default, 0.
  
  The Create Automation Wizard should resemble the following illustration.
  
  Description of the illustration create_automation_wizard_1.png
8. Click Next.
Define the source:
This example is based on the local table, EMP.
1. Data Source - Select the data source for this automation. Select Local Database.
2. Source Type - Select Table.
3. Table/View Owner - Select the owner of the table on which you are basing this automation.
4. Table/View Name - Select the table or view on which the automation will be based. For this example, select EMP (table).
5. Execute Action When - This option determines if Automation Actions should execute when the query returns rows, or when the query does not return rows. Select Rows returned.
  
  The Create Automation Wizard should resemble the following illustration.
  
  Description of the illustration create_automation_wizard_2.png
6. Click Create.
  
  The automation edit page appears. The following message displays at the top of the page:
  
  Automation has been created and is in "Disabled" state. Now create Actions to execute when the automation runs.
Edit the action for the first salary increase:
1. Scroll down to Actions.
  Note that the wizard created an action, named New Action.
2. Edit the new action, New Action:
  - Click the Edit icon adjacent to New Action.
    
    The Edit Action page appears.
  - Name - Enter a meaningful name such as Increase SAL for SALESMAN.
    
    The Edit Action page should resemble the following illustration.
    
    Description of the illustration edit_action_1.png
  - Code - Add code for the first salary (SAL) increase.
    begin update emp set sal = round(sal * 1.015, 1) where empno = :EMPNO; apex_automation.log_info('SAL for ' || :ENAME || ' increased.'); end;
    Note that the code will run on each row. In this example, each SQL UPDATE statement works on a single row based on the EMPNO primary key column. Also note the use of the APEX_AUTOMATION.LOG_INFO procedure . The LOG_INFO procedure writes a message with the "Information" level to the Execution Log for the automation. Available message levels as "Information" are "Warning" and "Error".
3. Server Side Condition - Create a server side condition for the salary increase.
  - Server Side Condition, Condition Type - Select Expression.
  - Language - Select PL/SQL.
  - Expression 1- Enter an expression to only execute the action when the salesman (SALESMAN) has a commission (COMM) of more than 500. Enter:
```
:JOB = 'SALESMAN' and :COM > 500
```
  - Execute for Each Row - Enable this option to specify the condition execute for each row.
4. Click Apply Changes.
Create another action the second salary increase:
1. Scroll down to Actions again and click Add Action.
  The Edit Action page appears.
2. Edit the new action:
  - Name - Enter a meaningful name such as Increase SAL for all others.
  - Code - Update the code to increase the salary (SAL) for all other salesman.
    begin update emp set sal = round(sal * 1.01, 1) where empno = :EMPNO; apex_automation.log_info('SAL for ' || :ENAME || ' increased by 1%.'); end;
3. Server Side Condition - Create a server side condition for the salary increase.
  - Condition Type - Select Expression.
  - Language - Select PL/SQL.
  - Expression - Enter an expression to only execute this action when the salesman (SALESMAN) has a commission (COMM) is less than or equal to 500 or null.
```
:JOB != 'SALESMAN' and :COM <= 500 or :COMM is null
```
  - Execute for Each Row - Enable this option to specify the condition execute for each row.
4. Click Create.
Run the automation. On the Automation Edit page, click Save and Run.

When you click Save and Run. the automations execute in the background. Note that the browser displays again immediately even if the actions are still running.

The following message appears:

Changes applied. Automation Execution initiated.
Review the Automation Execution Log:
1. Return to the Automations page by clicking the Automations breadcrumb.
2. Click the Execution Log tab and verify the automation executed.
3. Click the link under Messages to view individual messages for processed rows.
  
  Tip:
  
  You can also view the target table in SQL Workshop, Object Browser to validate that the automation is actually changing the data. See "Browsing a Table" in Oracle Application Express SQL Workshop Guide.
Clear the Automation Execution Log:
1. On the Execution Log page, click Purge Log.
  The Clear Automation Log dialog appears.
2. In the Clear Automation Log dialog:
  - From the Automation list, select the Automation (for example, Increase Sal).
  - Click Clear Log.
Update the automation Settings to enable the schedule:
1. Return to the Automations page by clicking Automations breadcrumb.
2. Select the name of the automation to be edited. For example, select Increase SAL.
3. Settings, Schedule Expression - Click Schedule Builder to right of the Schedule Expression field and update the following:
  - Frequency - Select Weekly.
  - Interval - Accept the default, 1.
  - Execution Day - Select Mon, Tue, Wed, Thu, and Fri.
  - Execution Hour - Enter 14.
  - Execution Minutes - Enter 30.
  - Click Set Execution Interval.
  In the previous example, the automation execution would be due every working day at 2:30pm. The automation scheduler runs in the time zone of the database server.
4. Schedule Status - Select Active.
5. To save your edits, click Save Changes.