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 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
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:
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
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) := 'email@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; /
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('firstname.lastname@example.org', '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.