Overview
Oracle Apex Automations enable applications to utilize the oracle job scheduler from a Graphical User Interface using a package built on top of the dbms_scheduler called APEX_AUTOMATION. To manage scheduled jobs and view the internals of the job scheduler can be a complicated process. Oracle APEX automations makes it a breeze.
Technologies and Tools Used
The following technologies have been used to compare objects.
- Oracle Apex
Use Case
Most businesses need the ability to receive alerts or update records when employees are terminated, or send notifications to the marketing team when a new business lead comes in. Automations are the way to handle these items. There are limitless capabilities using PL/SQL code togather data and transform that data. At a high-level overview, automations are a set of steps that can be ran on a schedule and trigger based off of the query results that are retrieved.
Steps with Screenshot
In this demonstration we will create an automation to insert records into another table when an employee has left the company. The demonstration below requires that you have a data set that has an employee table with information about the employee and a column containing termination date data. In our example that column is entitled: termination_date
Within the Application Builder:
Navigate to Shared Components > Automations > Create
Fill in the values as shown below to create “Terminated Employee” automation. You can customize the schedule as needed or use the basic built-in features. Make sure to update the schedule to the settings you need. For this demonstration, you can simply set to 15 minutes so that you can test the automation every 15 minutes
Next, we will create the data source for the automation:
Fill in the values as show below using the following query and select Create.
SELECT * FROM EMP WHERE TERMINATION_DATE > TRUNC(SYSDATE -1)
You have now setup the framework for the automation, and now we can proceed to define the action that initiates from the automation. You’ll notice the name and settings you selected, and the static name is generate for when manually calling the automation via the APEX_AUTOMATION PL/SQL package.
Select Add Action underneath the Actions section to begin imputing your code that will be performed when the scheduled time occurs.
You have now completed the Automation and can save the changes. The Automation is DISABLED on creation, so you will need to Enable the Automation by editing the automation and updating the Schedule Status underneath settings to Active. Remember, you set the schedule to a 15-minute schedule above, so make sure to update your record set if you are testing to have date greater than SYSDATE – 1
As you can see you can combine code for one process into one automation. You can add multiple actions to an automation, so if you needed to terminate an employee as well as make sure that they have filled out the termination paperwork, you can simply add another action and then have that action processed.
ADVANCED OPTIONS
As you can see creating an automation is an easy and self-explanatory process. Everything with Oracle APEX comes with an advanced option for those who are wanting to get a little bit more functionality out of the automation feature set. Below are some examples of other features.
Action Execution
Execute Actions When –Ability to run the automation if your query returns results
Primary Key Column –Useful for when debugging logs of automation for row set
Commit –Increase commit rate to pre row or all row sets at once
Maximum Rows to Process –Will stop processing after row set maximum is reached
Action Error Handling – Useful for aborting, or disabling the automation if you need to make sure all components of the automation are working as expected.
Additional Code Execution
This is beneficial area to do more/less a pre-step or post step, or even before each row is processes check. Let’s say we wanted to verify that the employee submitted their laptop & badge before leaving the company. You can enter the procedure stored in the database in the Before Row Processing Procedure Name to run the name of the PL/SQL box and provide the code in this region
This is only the beginning of the capabilities of what Oracle Automatons can do. You can expose the PL/SQL to run and extract data from websites, send text messages, or even POST messages using Oracles other features. Not only can you post and gather data, the automation can work in reverse. If you want to run the automation based off of something changing on website, you can setup a REST_data_source and return the record set from a web interface.
Conclusion :
Oracle APEX automatons simplify the management of scheduler jobs and provide a high-level interface for quickly scheduling jobs/requirements that your business needs to begin the automating of redundant and busy work. Your business will be stunned with the simple and yet user-friendly setup of an Oracle Automation.
