How to Send an Email using UTL_SMTP on Autonomous Database

Autonomous Database now supports UTL_HTTP and UTL_SMTP PL/SQL packages. You may already be familiar with these as they are commonly used in various scenarios. In this blog post, we will focus on how to send an email using the UTL_SMTP package. Before we dive into the details, it’s worth noting that both packages are subject to certain restrictions. Even though we’ll cover some of those here, you might still want to see Sending Email on Autonomous Database and PL/SQL Packages with Restrictions in the documentation.

The UTL_SMTP package is designed for sending emails over Simple Mail Transfer Protocol (SMTP) and it provides numerous interfaces to the SMTP commands (See UTL_SMTP for more details). Thanks to these interfaces, it’s in fact quite simple to send an email from within the database. However, as mentioned earlier, there are some restrictions that we need to be aware of. For example, the only supported email provider currently is Oracle Cloud Infrastructure (OCI) Email Delivery service. In other words, we need to have a working Email Delivery configuration before we can start sending emails on Autonomous Database (ADB). Let’s start!

Here’s the list of steps that we are going to follow to successfully send an email on ADB:

• Configure Email Delivery Service
• Allow SMTP Access for ADMIN via an Access Control Entry (ACE)
• Create a PL/SQL Procedure to Send Email (Updated on 12/21/22 with the new UTL_SMTP.SET_CREDENTIAL procedure!)
• Send a Test Email

Configure Email Delivery Service

Oracle Cloud Infrastructure Email Delivery is an email sending service that provides a fast and reliable managed solution for sending high-volume emails (See Overview of the Email Delivery Service for more details). In this step, we are going to configure Email Delivery in OCI console as shown below:

• Generate SMTP credentials for a user.
  • 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.
  • Click SMTP Credentials.
  • Click Generate SMTP Credentials.
  • Enter a Description of the SMTP Credentials in the dialog box.
  • Click Generate SMTP Credentials. A user name and password is displayed.

Note: Whether you create a new user (See Adding Users) or choose to use an existing user for these steps, you need to make sure the user is assigned to a group with permissions to manage approved-senders and suppressions (See Set Up Permissions for more details). For example, our user in this example is assigned to a group that has the following policies for approved-senders and suppressions:

Allow group <Your Group Name> to manage approved-senders in tenancy

Allow group <Your Group Name> to manage suppressions in tenancy

• Create an approved sender for Email Delivery. We need to do this for all email addresses we use as the “From” with UTL_SMTP.MAIL (See Managing Approved Senders for more information).
  • Open the navigation menu. Under Solutions and Platform, go to Email Delivery and click Email Approved Senders.
  • Click Create Approved Sender within the Approved Senders view.
  • Enter the email address you want to list as an approved sender in the Create Approved Sender dialog box.
  • Click Create Approved Sender. The email address is added to your Approved Senders list.

Allow SMTP Access for ADMIN via an Access Control Entry (ACE)

Now we need to append an access control entry (ACE) using the DBMS_NETWORK_ACL_ADMIN package for ADMIN user to access SMTP for a specific host and port:

Note: You can find your SMTP endpoint (host) and eligible ports by opening the navigation menu following Email Delivery –> Email Configuration.

begin
  -- Allow SMTP access for user ADMIN
  dbms_network_acl_admin.append_host_ace(
    host =>'smtp.us-ashburn-1.oraclecloud.com',
    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;
/ 

Create a PL/SQL Procedure to Send Email

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.username';
  passwd varchar2(50):= 'password';
  msg_from varchar2(50) := 'adam@example.com';
  mailhost VARCHAR2(50) := 'smtp.us-ashburn-1.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;
/

Notes:

• username: Specifies the SMTP credential username.
• passwd: Specifies the SMTP credential password.
• msg_from: Specifies one of the approved senders.
• mailhost: Specifies the SMTP connection endpoint.

BEGIN DBMS_CLOUD.CREATE_CREDENTIAL (
  credential_name => 'SMTP_CRED',
  username        => 'ocid1.user.oc1.username',
  password        => '************' );
END;

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

  mail_conn utl_smtp.connection;
  smtp_cred varchar2(50):= 'SMTP_CRED';
  msg_from varchar2(50) := 'adam@example.com';
  mailhost VARCHAR2(50) := 'smtp.us-ashburn-1.oraclecloud.com';

BEGIN
  mail_conn := UTL_smtp.open_connection(mailhost, 587);
  utl_smtp.starttls(mail_conn);
  
  UTL_SMTP.SET_CREDENTIAL(mail_conn, smtp_cred, 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;
/

Send a Test Email

In order to verify that we configured everything accurately and created a working procedure (SEND_MAIL), we will send a test email:

execute send_mail('taylor@example.com', 'Email from Oracle Autonomous Database', 'Sent using UTL_SMTP');

To summarize, UTL_SMTP package is now supported in Autonomous Database and we just explored how to send an email by taking advantage of this package the OCI Email Delivery service. If you’d like to learn more about Email Delivery or UTL_SMTP in ADB, please follow the documentation links referenced above.