In the older releases of Oracle APEX, it was not easy to schedule a background process in Oracle APEX. If there was a requirement to schedule a background job, we would need to write custom code using DBMS_SCHEDULER API which was introduced in Oracle 10g.
Today's Oracle APEX has a built-in feature to create Automations which is certainly a much more developer friendly solution.
The Automation feature can be used for variety of use cases such as Sending Email Notifications as per a set schedule, Sending Push Notifications at certain interval, submitting/running long running processes in background, sending alerts by monitoring critical business activities etc.
Let's see what are the steps to create APEX Automations.
- Create a new App
- Navigate to Shared Components and locate Workflows and Automations section and click on Automations
- Click Create
- This will initiate a Wizard to create an Automation
- Here, we can name our Automation and also mention whether it will be a Scheduled automation or it will execute On-Demand
Let's select Scheduled for our use case
We will get an option to decide whether the Action gets triggered based on the output of a query Or it should Always trigger.
And in the end, we can define the Schedule of this Automation
- Now we will be presented with a screen where we need to mention the table name based based on which the Automation will execute and we also mention that whether to run it if Rows are returned or Not Returned.
Let's say we have a custom table ORG_EMPLOYEES which holds employee information along with their Start Date and End Date. Now we want to create an Automation on this table to send an email if any employee has End Date in next 7 days. Let's see how to do it.
- Let's select SQL Query option and enter our query
Here, one can also utilize REST Enabled SQL or REST Data Source options instead of Local Database.
- Click Create
- We will see below message that says Automation has been created but it's in Disabled state
- We will also see all the details we entered so far in the below section
- Now let's create an Action in this Automation
- Scroll down to Actions section and click Add Action
- Let's select Send E-Mail in Type
- We can mention the standard Email parameters such as To, CC, BCC in this section
- Now, let's enter the Subject and Body for the Email Notification. In this case, it would be a message informing the recipient about Employee records set to expire soon.
- Click Create
- Now, let's click Save and Run. This will run the Automation as a one-off request. This is how we can test our Automation before Enabling it.
- We should receive the Email Notification like this.
- Now that the Automation has been tested successfully, we can Enable the Schedule so that it automatically runs in the background and will send email as per our conditions
This is how we can create a simple Automation in Oracle APEX to send Email Notifications. This feature has a big potential as it's not limited to sending notifications; instead we can call a PL/SQL procedure in the Action section so that we can execute custom code whenever an Automation runs and the process can take care of data processing, integrations as well as sending alerts etc. Since it will be a custom code, the possibilities are endless on what we can achieve through the Automation feature in Oracle APEX.