In this blog post, I will describe sending mail using UTL_SMTP on Autonomous Database. The only supported email provider is Oracle Cloud Infrastructure Email Delivery service.
Let’s do step by step
1- Identify your SMTP connection endpoint for Email Delivery. You may need to subscribe to additional Oracle Cloud Infrastructure regions if Email Delivery is not available in your current region.
For example, select one of the following for the SMTP connection endpoint:
I will select “smtp.email.eu-frankfurt-1.oci.oraclecloud.com” as SMTP connection endpoint.
2- Generate SMTP credentials for Email Delivery. UTL_SMTP uses credentials to authenticate with Email Delivery servers when you send email.
To generate SMTP credentials for a user;
a- Open the navigation menu. Under Governance and Administration, go to Identity and click Users. Locate the user in the list that has permissions to manage email, and then click the user’s name to view the details.
b- Click user link
c- Click SMTP Credentials and Generate SMTP Credentials.
d- Enter a Description of the SMTP Credentials in the dialog box.
e- Click Generate SMTP Credentials. A user name and password is displayed.
f- Copy the user name and password for your records and click Close.
3- Create an approved sender for Email Delivery. Complete this step for all email addresses you use as the “From” with UTL_SMTP.MAIL.
To create an approved sender for Email Delivery;
a- Open the navigation menu. Under Solutions and Platform, go to Email Delivery and click Email Approved Senders. Ensure that you are in the correct compartment. Your user must be in a group with permissions to manage approved-senders in this compartment.
b- Click Create Approved Sender within the Approved Senders view.
c- Enter the email address you want to list as an approved sender in the Create Approved Sender dialog box.
d- Click Create Approved Sender. The email address is added to your Approved Senders list.
4- Allow SMTP access for ADMIN user by appending an Access Control Entry (ACE).
host => ‘*’,
lower_port => 587,
upper_port => 587,
ace => xs$ace_type(privilege_list => xs$name_list(‘SMTP’),
principal_name => ‘ADMIN’,
principal_type => xs_acl.ptype_db));
5- let me create a PL/SQL procedure to send email.
CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_text varchar2 )
username varchar2(1000):= ‘ocid1.user.oc1.user’;
passwd varchar2(50):= ‘passs’;
msg_from varchar2(50) := ‘firstname.lastname@example.org’;
mailhost VARCHAR2(50) := ‘smtp.email.eu-frankfurt-1.oci.oraclecloud.com’;
mail_conn := UTL_smtp.open_connection(mailhost, 587);
UTL_SMTP.AUTH(mail_conn, username, passwd, schemes => ‘PLAIN’);
UTL_SMTP.write_data(mail_conn, ‘Date: ‘ || TO_CHAR(SYSDATE, ‘DD-MON-YYYY HH24:MI:SS’) || UTL_TCP.crlf);
UTL_SMTP.write_data(mail_conn, ‘To: ‘ || msg_to || UTL_TCP.crlf);
UTL_SMTP.write_data(mail_conn, ‘From: ‘ || msg_from || UTL_TCP.crlf);
UTL_SMTP.write_data(mail_conn, ‘Subject: ‘ || msg_subject || UTL_TCP.crlf);
UTL_SMTP.write_data(mail_conn, ‘Reply-To: ‘ || msg_to || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.write_data(mail_conn, msg_text || UTL_TCP.crlf || UTL_TCP.crlf);
WHEN UTL_smtp.transient_error OR UTL_smtp.permanent_error THEN
WHEN OTHERS THEN
6- Now let me send a test email using the PL/SQL procedure.
execute send_mail(‘email@example.com’, ‘Email from Oracle Autonomous Database’, ‘Sent using UTL_SMTP’);