How to configure Email Delivery in OCI and send emails from APEX deployed in Oracle Autonomous Database

In this Blog, we will learn how to set up Email Delivery configurations in Oracle Cloud Infrastructure (OCI) and use the same in APEX which is deployed in Autonomous Database on this OCI environment.

Part 1: Configurations in OCI

Step 1: Creating a Group

Navigate to Identity & Security -> Groups

Create a new Group. We'll name it MailGroup

Step 2: Creating an User

Navigate to Identity & Security -> Users

Now, let's create a new Identity and Access Management (IAM) User as shown below. We'll name it mailsender@dummydomain.com

Step 3: Adding User to the Group

- Navigate to Groups.
- Open MailGroup
- Click on Add User to Group
- Select mailsender@dummydomain.com to add it to this group

Step 4: Create Email Policy / User Permissions

Navigate to Identity & Security -> Policies

- Create a new Policy. We'll name it MailPolicy
- Set Policy use case to Email Management
- Select/assign MailGroup under Groups section

Step 5: Create SMTP Credentials

- Navigate to User Profile
- Click on User Settings

- Navigate to Resources section and click on SMTP Credentials

- Click on Generate SMTP Credentials

- Enter a Description of the SMTP Credentials in the dialog box. We'll name it MailSender

- Click Generate SMTP Credentials. A user name and password is displayed.


IMPORTANT:

Copy the user name and password for your records before closing the dialog box. This is very important because you can't retrieve the password again after closing the dialog box for security reasons.


Step 6: Create Email Domain

- Navigate to Developer Services -> Email Delivery

- Click on Create Email Domain
- We'll name it dummydomain.com
- Create

Step 7: Create an Approved Sender

- Navigate to Approved Senders section
- Create Approved Sender
- We'll name it mailsender@dummydomain.com
- Please note that this will be the email address which will be used to send emails from OCI and/or APEX. 

Step 8: Check SMTP Configuration

- Navigate to Configuration under Email Delivery section
- Copy the Public Endpoint from SMTP Configuration

Part 2: Configurations in APEX

Step 1: Set APEX Instance Parameters

- Connect to the Autonomous Database as ADMIN user (SQL Developer in OCI)

- Run the following command using the Public Endpoint, Username and Password generated in above steps:

BEGIN

APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_HOST_ADDRESS', 'smtp.email.us-ashburn-1.oci.oraclecloud.com');

APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_USERNAME', '<username from above steps>');

APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_PASSWORD', '<password from above steps>');

COMMIT;

END;

/

We've now completed all the configurations pertaining to OCI and APEX for Email Delivery.

Now, let's see how to send emails from APEX application.


Step 2: Send Email

- To send emails in APEX, we've to use APEX_MAIL.SEND API

Here's the API definition:

APEX_MAIL.SEND(
    p_to                        IN    VARCHAR2,
    p_from                      IN    VARCHAR2,
    p_body                      IN  [ VARCHAR2 | CLOB ],
    p_body_html                 IN  [ VARCHAR2 | CLOB ] DEFAULT NULL,
    p_subj                      IN    VARCHAR2 DEFAULT NULL,
    p_cc                        IN    VARCHAR2 DEFAULT NULL,
    p_bcc                       IN    VARCHAR2 DEFAULT NULL,
    p_replyto                   IN    VARCHAR2);

- Navigate to APEX on OCI

- Navigate to SQL Commands window under SQL Workshop

- Enter below code and run

BEGIN

APEX_MAIL.SEND

(

    p_to   => 'xyz@gmail.com'

   ,p_from => 'mailsender@dummydomain.com'

   ,p_subj => 'Test Notification Subject'

   ,p_body => 'Test Notification Body'

);

COMMIT;

APEX_MAIL.PUSH_QUEUE;

END;

/

Please note that we've used mailsender@dummydomain.com as the From Email as it was configured as an Approved Sender in OCI. Email delivery will fail if any other Email Id is used for From Email parameter.

- Once above steps are completed, the Email Notification should be successfully delivered to the designated mailbox: