v.3 Full Tutorial: Configuring Email Sending on OCI Free Tier with Oracle APEX on Autonomous Database

First: What is OCI Email Delivery?

Oracle Cloud Infrastructure (OCI) Email Delivery is a service designed to send transactional and high-volume emails securely and reliably. This article will guide you through the process of setting up OCI Email Delivery to send emails directly from your Oracle APEX Autonomous Database. By the end of this guide, you will be able to successfully configure and test email delivery.

Objective

Configure OCI email delivery.
Integrate the service with Oracle APEX.
Test email delivery.
Troubleshooting Sending Issues

Before you start

Make sure you have:

A free Oracle Cloud Infrastructure (OCI) account.
Administrator privileges.
An active Oracle APEX service.

Notice

OCI often updates the names, order, and placement of services in the menu. Therefore, the paths described in this article may differ slightly by the time you read it. However, a quick search in the service search bar should help you find the configuration options you need.

Let’s get started:

1. Create or select a domain

Once you are logged in to OCI with your administrator account.

If you haven’t added a domain yet, do so now. If multiple domains are available, select the one currently in use.

Go to: Menu > Identity & Security > Domains

2. Create a user group

Navigate to: Menu > Identity & Security > Domains > Oracle Identity Cloud Service > Domain > Groups > Create Group

For example: Create a group named My-Email-Group.

3. Create a user and add him to the group (one step) ####

Navigate to: Menu > Identity & Security > Domains > Oracle Identity Cloud Service > Domain

4. Create an email policy

Go to: Menu > Identity & Security > Policies and associate it with the user group you created

5. Create credentials for your SMTP service

Go to: Menu > Identity > Domains > your domain > Users > SMTP

Copy the username and password and store them safely, as you will need them soon.

6. Create an email domain

Don’t confuse this with a normal domain.

Go to: Menu > Developer Services > Sending Emails

Click Create Email Domain.

7. Create a user to approve your emails

Go to: Menu > Developer Services > Sending Emails

A trusted sender is the email address used in the “From:” header of emails you send. Each sender email address must be registered in order to be used for email delivery.

Example: [email protected] or [email protected]

8. Get SMTP settings

Oracle Message:

For optimal sending reputation, set up DKIM for this email domain. This is an important step to ensure emails are delivered and arrive in the inbox.

Do not skip this step unless you are in a development environment, in a hurry, or are not very concerned about security.

A suggestion: why not continue the setup, test it, send a test email? If it works, come back and finish the DKIM part.

Go to: Menu > Developer Services > Email Distribution > Configuration

In the SMTP sending information (not the first HTTPS sending information), copy your new:

Public endpoint
Port

Stay calm. Long setups can be tedious, but we’re almost done!

9. Configure SMTP service in Autonomous

Run the following command in:

Oracle Apex / SQL Workshop / SQL Commands or
SQL Developer
Or any environment with administrator permission and standalone access.

BEGIN 
    APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_HOST_ADDRESS', 'paste_your_smtp_endpoint_here'); 
    APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_USERNAME', 'enter_your_username_here'); 
    APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_PASSWORD', 'enter_your_password_here'); 
    COMMIT; 
END;

10. Testing Email Delivery

Now it’s time to test. If everything is set up correctly, your email should be sent.

As in the previous step, choose your preferred location for sending the test:

BEGIN 
    APEX_MAIL.SEND ( 
        P_To    => '[email protected]', 
        P_Cc    => '[email protected]', 
        P_From  => 'your_approved_sender@your_email_domain', 
        P_Subj  => 'OCI Notification Test via Apex', 
        P_Body  => 'Worked' 
    ); 
    COMMIT; 
    APEX_MAIL.PUSH_QUEUE; 
END;

APEX_MAIL.PUSH_QUEUE:

This instruction queues email messages to be sent. It processes emails that have been configured but not yet sent, allowing you to manage and send email communications from your APEX application.

Some people manage to configure the command correctly and send the email, but the message does not reach the recipient. The developer may then think that there is a problem with the environment, configuration, or execution of the command.

And in some cases, it’s actually just the email that was sent to the queue and wasn’t executed immediately.

11. Checking for sending problems

To check for sending problems…

Go to: Menu > Manage Instance > Mail Queue

If the email does not appear in the queue or is not sent, check the following:

Make sure APEX_MAIL.PUSH_QUEUE has been executed.
Check if there are any errors in the sender name.

Try another curious test.

If the recipient (entity that will receive the message) does not exist,

The email will not show any sending errors. Only an error in the sender name will generate a sending error.

When you press the “Force Send All Emails” button, if the queue is empty, it means that the email was sent successfully, because it was already ready to be sent and waiting in the queue.

12. Import queries for analysis

You can also check the full details of all messages using the administrative views:

To view live queue status:

SELECT * FROM APEX_MAIL_QUEUE;

If no records were found, that’s good. This means nothing is stuck in the queue.

The most important.

To view the processing of all sent or unsent emails:

SELECT * FROM APEX_MAIL_LOG;

13. OCI Email Delivery Dashboard

After sending multiple messages, you can check more details in the Email Deliverability and Reputation Governance Dashboard in OCI.

Go to: Menu > Developer Services > Sending Emails

If all cards do not show any activity, change the “Filter by time” setting. Refreshing the page may show some data

14. Sending from your Oracle APEX application

If you are writing a process in Oracle APEX to send an email, using a template or PL/SQL code, enable the send immediately option so that the message does not get stuck in the queue.

Send Immediately option: active

In fact, the image below shows all the settings you need to send a canned email from any Oracle Apex page. Just click any button and go! This is the easiest and fastest way to do it.

In fact, the image below shows all the settings you need to send a canned email from any Oracle Apex page. Just click any button and you are ready to go! This is the easiest and fastest way to do it. Don’t forget to add a button to your page that triggers sending a canned email from any Oracle Apex page. send_mail_without_PLSQL process.

An alert…

The email queue serves multiple purposes. If every application sent emails immediately, it could lead to performance issues. So it might be a good idea to understand the company culture or create one.

One way to do this is to talk to your database administrator, infrastructure person, or a colleague who has been with the company longer, to determine the best strategy for each environment (development, production, or staging).

Conclusion

Congratulations! You have successfully configured OCI Email Delivery and integrated it with Oracle APEX. To ensure efficient and secure email delivery, consider setting up DKIM and monitoring your sending reputation.

If you encounter problems or need additional assistance, consult the official Oracle documentation or contact technical support.

Cover image credit:

Image by creativeart in Freepik

Thank you for reading and taking the time!