Complete Tutorial: Setting up email sending on OCI Free Tier with Oracle APEX on Autonomous Database

Cover Image credit: Imagem de creativeart no Freepik

First: What is OCI Email Delivery?

Oracle Cloud Infrastructure (OCI) Email Delivery is a service designed for sending transactional and high-volume emails securely and reliably. This article will guide you through the process of configuring OCI Email Delivery to send emails directly from your Oracle APEX Autonomous Database.

Objective

1. Configure OCI Email Delivery.
2. Integrate the service with Oracle APEX.
3. Test email delivery.
4. Troubleshoot sending issues

Before you Start

Ensure you have:

• A free-tier 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. As a result, the paths described in this article might 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 start:

1. Create or Select a Domain

Once you’re logged into 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 and Security > Domains

If no information is displayed, verify that a valid compartment is selected.

2. Create a User Group

Go to: Menu > Identity and 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 them to the group (one step)

Go to: Menu > Identity and Security > Domains > Oracle Identity Cloud Service > Domain

4. Create an Email Policy

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

5. Create credentials for your SMTP service

Go to: Menu > Identity > Domains > your domain > Users > SMTP
Copy the user and password and store them securely, as you'll need them soon.

6. Create an email domain

Don’t confuse this with a regular domain.

Go to: Menu > Developer Services > Email Delivery

Click on Create Email Domain.

7. Create a user to approve your emails

Go to: Menu > Developer Services > Email Delivery

An Approved Sender is the email address used in the "From:" header of the emails you send. Each sender email address must be registered to use it for Email Delivery.

Example: noreply@yourdomain.com or sender_email@yourdomain.com

8. Obtain SMTP parameters

Oracle Message:
For optimal sending reputation, configure DKIM for this email domain. This is an important step to help ensure email delivery and reach the inbox.

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

A suggestion: Why don't you keep going with the setup, test it out, send a test email? If it works, come back and finish the DKIM part.

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

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

• Public endpoint
• Port

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

9. Configure the SMTP Service in Autonomous

Execute the following command in:

• Oracle Apex / SQL Workshop / SQL Commands or
• SQL Developer
• Or any environment that has admin permission and access to the autonomous.

It is now time to paste the SMTP credentials that you got in steps 5 and 9:

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    => 'any@gmail.com',
        P_Cc    => 'another@gmail.com',
        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 for sending. It processes emails that have been configured but not yet sent, allowing you to manage and dispatch email communications from your APEX application.

Some people manage to set up the configuration correctly and send the email, but the message doesn’t reach the recipient. As a result, the developer might believe 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 not executed immediately.

11. Checking Sending Issues

To check for sending issues...

Go to: Menu > Manage Instance > Mail Queue

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

• Ensure APEX_MAIL.PUSH_QUEUE was executed.
• Verify if there are errors in the sender's name.

Try another curious test.

If the recipient (entity that will receive the message) does not exist,
the email will not show sending errors. Only a failure in the sender’s name will generate a sending error.

When you press "Force Send All Mail" button, if the queue is empty, it means the email was successfully sent, as it was already ready for dispatch and waiting in the queue.

12. Important Queries for Analysis

You can also check complete details of all messagees using the administrative views:

To view the live status of the queue:

SELECT * FROM APEX_MAIL_QUEUE;

If no records were found, that's good. It means that 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 Deliver Dashboard

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

Go to: Menu > Developer Services > Email Delivery

If all cards show zero activity, change the 'Filter by time' setting. Refreshing the page could display some data

14. Sending from you Oracle APEX app

if you are writing a process in Oracle APEX to send an email, using a template or PL/SQL code, enable the option to send immediately so that the message doesn’t get stuck in the queue.
Option Send Immediately: Active.

Actually, the image below shows all the settings you need to send a standardized email from any Oracle Apex page. Just click any button and you're good to go! It's the simplest and fastest way to do it. Don't forget to add a button to your page that triggers the send_mail_without_PLSQL process.

An alert...

The email queue serves several 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 to establish one.

One way to do this is by talking to your database administrator, the person in charge of infrastructure, or a colleague who has been with the company longer, to figure out 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 effective and secure email delivery, consider configuring DKIM and monitoring your sending reputation.

If you encounter issues or need further support, consult Oracle’s official documentation or contact technical support.

Thanks for reading and for you time!