Sending emails using an APEX application- part 1

This article summaries the steps taken to configure APEX application to send emails.

We shall use the email delivery service from Oracle Cloud.

New to Oracle cloud? Get a free trial version of Oracle cloud here.

The assumption is that you have already configured your IAM policies correctly ie. the user we use is in a group (ie. MailGroup ) which has a policy passed to manage approved mail senders.

Allow group MailGroup to manage approved-senders in compartment CompartmentA /tenancy etc.

Learn more about Oracle Email delivery policies here.

Let’s now set up the user SMTP credentials.

To generate SMTP credentials of the user to use in our email delivery, navigate to the user’s profile on Oracle Cloud -> Identity -> Users -> select your user.

On the left side click SMTP Credentials -> Click on generate credentials.

Save the username and password generated. You shall use them later on configuring your APEX application.

Add the user on the Email approved Senders’ List of Oracle Email Delivery.

Navigate to your cloud console to Email Delivery -> Email Approved senders- > create approved sender. (add the user)

Next click on the Email configuration to get the SMTP connection endpoint. (Save it for later use)

ie. smtp.email.eu-frankfurt-1.oci.oraclecloud.com

Set Email Parameters on the Database

Connect to your Database as ADMIN and execute the PL/SQL script below. If you are using Autonomous database, you can connect to your DB using SQL Developer Web.

BEGIN
APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_HOST_ADDRESS', 'Your SMTP connection endpoint ie. smtp.email.eu-frankfurt-1.oci.oraclecloud.com');
APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_USERNAME', 'smtp username generated');
APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_PASSWORD', 'smtp password generated');
COMMIT;
END;
/

Send Email from APEX

Now login to APEX and navigate to SQL workshop to run this command.

BEGIN
apex_mail.send(p_from => 'your approved email sender',
p_to => 'ncve3@gmail.com',
p_subj => 'Email from Autonomous',
p_body => 'It worked!!');
apex_mail.push_queue();
END;
/

Once you run that PL/SQL an email is sent to your client!

Monitoring

You can monitor the mail logs using these commands or using your APEX admin dashboard (Monitor activity\ Mail Log).

Select * from apex_mail_log;

SELECT * FROM APEX_MAIL_QUEUE;

Next

We shall use the now configured email functionality to send emails from APEX application process.

References

Sending Emails from APEX by Sinan

Sending Emails from your APEX by Chaitanya Koratamaddi

Author: Labanish

I am a Principal Account Cloud Engineer at Oracle. Am interested in Cloud Automation, Cloud Native Applications dev and Emerging Tech. I have over 6 years work experience handling different tech roles in East Africa, Romania & now in United Kingdom. All views are mine.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: