Oracle APEX Automations are a sequential set of PL/SQL actions, triggered by query results. They are used to monitor data and then perform the appropriate action (examples are auto-approving specific requests and sending email alerts). An automation can be triggered on Schedule or on Demand, by invoking the APEX_AUTOMATION package. Query results can be derived from:
To explore Automations further in this blog, I'm going to utilize the EMP / DEPT Sample Dataset. So, first, install the Sample Dataset using the followings:
Please note for this action to work, you need to configure the email server in APEX to be able to send emails. If you are using apex.oracle.com, this is already set and pre-configured for you.
Inspired by the tweets from my colleague Patrick Wolf, I have created a test case for a common business process. A new employee is joining a department in your organization and you would like automating notifying his/her manager to take the necessary actions. The automation will run on a daily basis looking for employees that their hiredate will be tomorrow and send an email to their manager.
To achieve that we will need to build a few things.
We will set the Automation's Schedule to run on a daily basis.
Let's create an email template by following these steps:
Header
<b style="font-size: 24px;">New Hires Reminder</b>
Body
<b>Hi #MANAGER#,</b><br> <br> <b>You have got a new hire.</b><br> <br> <table width="100%"> <tr> <th align="left">Hire Date</th> <td>#HIREDATE#</td> </tr> <tr> <th align="left">Name</th> <td>#EMPLOYEE#</td> </tr> <tr> <th align="left" valign="top">Department</th> <td>#DEPARTMENT#</td> </tr> </table> <br> <b>Please proceed with induction process...</b><br> <br>
Plain Text Format Content
Hi #MANAGER#, You have got a new hire. Hire Date: #HIREDATE# Name: #EMPLOYEE# Department: #DEPARTMENT# Please proceed with induction process...
The query we will use to fetch our information is as follows:
select e.empno, e.ename, m.ename as mgr_name, d.dname as department, e.hiredate from emp e inner join emp m on m.empno = e.mgr inner join dept d on d.deptno = e.deptno where e.hiredate = trunc(sysdate + 1);
The above SQL code will allow us to get the details for the employees where their hiredate is about to start tomorrow. Notice how we are self-joining the emp table to get the manager details of the employee.
If you run the query on the newly installed dataset, you will find that it returns no rows. That is to be expected. None of the employee records have a hiredate set for tomorrow's date. Let's change that fact and update the record for employee number to have a hiredate to tomorrow's date (as of the writing of this post, tomorrow's date is 12 of November 2020).
To do so, simply follow these steps:
Now navigate to SQL Workshop then click on SQL Commands and run the above query. You will find that there is a record returned.
We are ready now to build the Automation. We have the triggering element which is the new employees query result that will start their work tomorrow. And we have the Email Template that we will use as an Action as a result of the trigger.
Let's start building our Automation with the following steps:
The Automation has been created in the disabled mode, as no Actions have been defined yet.
Note that you can click on the small gear icon to change the schedule of the automation. You can set a highly flexible schedule based on Weekly, Daily, Hourly, or even Minutely options with the ability to set the interval for any of those choices.
We will not change the schedule and we will simply keep the daily midnight schedule.
Let's add an Action and Enable the Automation. To do so you can modify the empty Action created for us by default.
Click on the Edit Icon next to New Action under the Actions section as shown below.
Set the following values for the Action.
begin apex_mail.send ( p_to => 'YOUR_EMAIL',--:MGR_NAME || '@somewhere.com', p_template_static_id => 'NEW_HIRES_REMINDER', p_placeholders => '{' || ' "HIREDATE":' || apex_json.stringify( :HIREDATE ) || ' ,"MANAGER":' || apex_json.stringify( :MGR_NAME ) || ' ,"EMPLOYEE":' || apex_json.stringify( :ENAME ) || ' ,"DEPARTMENT":' || apex_json.stringify( :DEPARTMENT ) || '}' ); end;
You will notice a message indicating that the changes have been applied and the Automation Execution has been initiated.
Finally, to activate the Automation's schedule navigate to Settings of the Automation and set the Schedule Status to Active, and click Save Changes.
If everything goes well, you should be able to receive an email similar to the following:
The Automation will run now on daily basis checking for employees' hire dates and notifying their managers. You can always view the log of the Automation in the Execution Log tab. Application Express purges log information by default every 14 days.
It is true that you can do most of the above using DBMS_SCHEDULER. We have done that many times before. But, the Automations feature provides a simplified and enhanced experience to automate your process. It makes it much easier to schedule repeating tasks without digging deep into DBMS_SCHEDULER package or the need to have privileges to that package.
Combine Automations with other Oracle APEX APIs to unlock unlimited possibilities.
Happy APEXing!