Many Oracle APEX applications require the automation of user processes or the intervention of the application core to perform some independent tasks. Built-in processes in APEX engines can help with this – they can even be an inspiration to create RPA (Robot Process Automation) applications. But how to use them?

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.

A screen showing how to find the Shared Components section.

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

A screen showing how to find Email Templates.

A screen showing how to create Email Templates.

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

A screen showing fields that need to be filled out.

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>
 <b>You have new request to approve ! </b> <br>
 <table width="100%">  
     <th align="left">Request date </th>
     <td>#REQUESTDATE# </td>
     <th align="left"> Requester </th>
     <td>#REQUESTER# </td>
     <th align="left" valign="top">Object </th>
     <td>#OBJECT# </td>

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).

A screen showing the Plain Text Format.

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.

A screen showing where to find 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.

A screen showing how to create automation process.

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.

A screen showing task configuration.

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


    apex_mail.send (
        p_to                 => '',
        p_template_static_id => 'REMINDER_ABOUT_PENDING_APPROVALS',
        p_placeholders       => '{' ||
        '    "ORDER_NUMBER":'        || apex_json.stringify( x.ORDER_NUMBER ) ||
        '   ,"APPROVER":'            || apex_json.stringify( '' ) ||
        '   ,"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;

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.

A screen showing where to find the logs.

Logs are presented as visible on the screen below.

A screen showing the log.

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


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