My name is Szymon Kmieć and I’m an Oracle APEX Developer working at Pretius. A while ago I wrote an article about integrating APEX with the Stripe payment platform. Today, I want to deal with a completely different subject. In this article, I’ll show you the basics of automation mechanisms in APEX. I’ll explain the following things:

1. How to configure and use automated processes
2. How to check the process execution logs
3. How to set automation up as a reminder for an employee

Configuring the automation

Prepare your email template

Our goal will be to prepare an automation process that will be responsible for informing a user about “pending approvals” via email. Because of this, we need to start by preparing an email template. To do this, go to Shared components in the Oracle APEX App Builder.

Select Email Templates in the User Interface section and then click the Create Email Template button.

You’ll need to fill in some mandatory fields – Template Name, Static Identifier, and Email Subject – use the information provided below.

After that, you’ll also need to fill in information about the header, body, and footer of your new email template. For the header, paste the following line:

<b style="font-size: 24px;">Pending approval reminder ! </b>

For the body, use the code below:

<b>Hi #APPROVER#, </b> <br>
 <br>
 <b>You have new request to approve ! </b> <br>
 <br>
 <table width="100%">  
   <tr>
     <th align="left">Request date </th>
     <td>#REQUESTDATE# </td>
   </tr>
   <tr>
     <th align="left"> Requester </th>
     <td>#REQUESTER# </td>
   </tr>  
   <tr>
     <th align="left" valign="top">Object </th>
     <td>#OBJECT# </td>
   </tr>  
 </table>
 <br>
 
 <br>

And finally, use this line for the footer:

<a href="#MY_APPLICATION_LINK#">Check approvals now !</a>

You can check the results of these actions in the Plain Text Format section, where the final message sent to the e-mail will be visible. The values between the “#” chars will be filled with the parameter values from the apex_mail.send procedure, whose parameter determining the use of these parameters will be the template name ( REMINDER_ABOUT_PENDING_APPROVALS).

Prepare the automation process

We’re now ready to prepare the automation process. To do this, go once again to Shared Components in the App Builder. However, this time select Automations in the Workflows and Automations section.

With the previous steps, we have prepared elements such as a trigger (a new request, for example, for a new laptop), and the expected trigger result (like sending an email using the previously configured message template). So let’s set the process up!

Basic setup

We start by clicking the Create button in the Automations section and filling in the information visible on the screen below.

Next, we have to indicate when the automation is about to be launched. Our trigger is “pending approvals”, which means the automation should check if there are records in the database that have the status “PENDING”. To prepare this status, use the query below in the Source section.

Select 1 from EBA_DEMO_APPR_LAPTOP_REQUESTS where status = ‘PENDING’.

Each time the above query returns at least one row, the action in the automation process will be executed (or actions if several of them are added).

Task configuration

The next step is task configuration. Our goal is to send each pending request as an email in a loop. To do this, we need to configure the action as seen on the screen below.

Then, in the code section, program the action as follows:

begin

for x in (Select id ORDER_NUMBER, 'SKMIEC@pretius.com' APPROVER , NEED_BY REQUESTDATE, EMPNO REQUESTER, LAPTOP_TYPE OBJECT from EBA_DEMO_APPR_LAPTOP_REQUESTS where status = 'PENDING')
loop
    
    apex_mail.send (
        p_to                 => 'SKMIEC@pretius.com',
        p_template_static_id => 'REMINDER_ABOUT_PENDING_APPROVALS',
        p_placeholders       => '{' ||
        '    "ORDER_NUMBER":'        || apex_json.stringify( x.ORDER_NUMBER ) ||
        '   ,"APPROVER":'            || apex_json.stringify( 'SKMIEC@pretius.com' ) ||
        '   ,"REQUESTDATE":'         || apex_json.stringify( x.REQUESTDATE ) ||
        '   ,"REQUESTER":'           || apex_json.stringify( x.REQUESTER ) ||
        '   ,"OBJECT":'              || apex_json.stringify( x.OBJECT ) ||
        '   ,"MY_APPLICATION_LINK":' || apex_json.stringify( apex_mail.get_instance_url || apex_page.get_url( 10 )) ||
        '}' );

end loop;
end;

If you configure the action like this, it will cause notifications about all requests for new laptops to be sent every hour to the email address indicated in the code. The message will look like the one you see on the screen below.

Accessing logs

Built-in APEX mechanisms will now allow us to check logs from the execution of a given automation process. They are available in the Execution Logs tab, on the main level of the Automation section in the App Builder.

Logs are presented as visible on the screen below.

Here’s what specific columns mean:

• Start Timestamp – the moment of automation  launch, i.e. when the query was launched to check whether the action should be run (see section Basic setup)
• Automation – the name of executed automation
• Status – one of Success / Error / In Progress
• Successful Rows – number of correct rows/actions which was returned from Automations
• Error Rows – number of failure rows/actions which were returned from Automations
• Messages – In the case of Errors in Automations, APEX will return a message with an error signature

Conclusion

As you can see, Oracle APEX offers plenty of powerful, built-in automation capabilities that can help you deal with user processes and various independent tasks. Making use of these features is quite straightforward as long as you know the basics, and I hope this article will serve as a solid introduction to working with the apex_automation package. 

If you have any further questions, check out the Oracle APEX documentation. With its help, you can enable or disable the procedure, start it, change its schedule or check the execution logs, among other things. You can also reach out to me at skmiec@pretius.com. Finally, there are many other APEX-related articles on the Pretius blog, so check them out:

1. Maps in Oracle APEX – introduction for beginners
2. Oracle APEX new features – the low-code platform keeps evolving
3. Google Identity Services – a quick guide on using the new library in Oracle APEX applications
4. What is Oracle APEX? Possibilities and career paths for low-code developers