Send Email from APEX deployed in Oracle Autonomous Database

In the Oracle Cloud, you can provision an Autonomous Database in minutes and start developing your APEX application immediatelly. If you don’t have an Oracle Cloud Account, register for the Always Free resources.

From APEX, you can use the APEX_MAIL package to send emails using Oracle Cloud Infrastructure Email Delivery service.

Check how easy it is to configure!!!

Step 1: In Identity and Access Management (IAM) create a user mailsender@example.com, a group MailGroup, add the user to the group, and create a policy MailPolicy with the following statement:

Allow group MailGroup to use approved-senders in compartment CompartmentA

No alt text provided for this image

Step 2: add mailsender@example.com as approved sender in Email Delivery

No alt text provided for this image

Select CompartmentA from the top-down menu on the left side, click on Create Approved Sender, and enter the email address.

Step 3: Create the SMTP credentials for the user. From the user’s details page, click SMTP Credentials, and then Create SMTP Credentials

No alt text provided for this image

After entering a name, you will be prompted with a username and password. Copy these values to a safe place as there will not be displayed again.

No alt text provided for this image

Step 4: Connect to you Autonomous Database as ADMIN user and execute the following command using the username and password just got generated in step 3:

BEGIN
APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_HOST_ADDRESS', 'smtp.email.eu-frankfurt-1.oci.oraclecloud.com');
APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_USERNAME', 'username_from_step_3');
APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_PASSWORD', 'password_from_step_3');
COMMIT;
END;
/

You also could use a different SMTP Connection Endpoint than Frankfurt.

Step 5: Connect to APEX SQL Workshop and run the following command using the mail address from Step 2 in the p_from parameter:

BEGIN
APEX_MAIL.SEND(p_from => 'mailsender@example.com',
p_to  => 'recipient@example.com',
p_subj => 'Email from Oracle Autonomous Database',
p_body => 'Sent using APEX_MAIL');
END;
/

You’re done! šŸ™‚

In case something went wrong and the email has not been sent, check the mail queue in the database by querying:

SELECT mail_to, mail_send_error FROM apex_mail_queue;

Check this page for more details!

Would you like to get notified when the next post is published?