How To Send Mail with Email Delivery on Autonomous Database?

Hello,

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:

smtp.us-phoenix-1.oraclecloud.com
smtp.us-ashburn-1.oraclecloud.com
smtp.email.uk-london-1.oci.oraclecloud.com
smtp.email.eu-frankfurt-1.oci.oraclecloud.com

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).

BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_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));
END;
/

5- let me create a PL/SQL procedure to send email.
For example,

CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_to varchar2,
msg_subject varchar2,
msg_text varchar2 )
IS

mail_conn utl_smtp.connection;
username varchar2(1000):= ‘ocid1.user.oc1.user’;
passwd varchar2(50):= ‘passs’;
msg_from varchar2(50) := ‘autonomous@taliphakanozturk.com’;
mailhost VARCHAR2(50) := ‘smtp.email.eu-frankfurt-1.oci.oraclecloud.com’;

BEGIN
mail_conn := UTL_smtp.open_connection(mailhost, 587);
utl_smtp.starttls(mail_conn);

UTL_SMTP.AUTH(mail_conn, username, passwd, schemes => ‘PLAIN’);

utl_smtp.mail(mail_conn, msg_from);
utl_smtp.rcpt(mail_conn, msg_to);

UTL_smtp.open_data(mail_conn);

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);

UTL_smtp.close_data(mail_conn);
UTL_smtp.quit(mail_conn);

EXCEPTION
WHEN UTL_smtp.transient_error OR UTL_smtp.permanent_error THEN
UTL_smtp.quit(mail_conn);
dbms_output.put_line(sqlerrm);
WHEN OTHERS THEN
UTL_smtp.quit(mail_conn);
dbms_output.put_line(sqlerrm);
END;
/

6- Now let me send a test email using the PL/SQL procedure.
execute send_mail(‘dba@taliphakanozturk.com’, ‘Email from Oracle Autonomous Database’, ‘Sent using UTL_SMTP’);