1. Using Oracle Autonomous Database on Shared Exadata Infrastructure
  2. Using Autonomous Database
  3. Sending Mail with Email Delivery on Autonomous Database

11 Sending Mail with Email Delivery on Autonomous Database

Describes how to send mail using UTL_SMTP on Autonomous Database.

Topics

Send Mail Using Email Delivery on Autonomous Database

Describes the steps to send email using UTL_SMTP on Autonomous Database.

Note:

The only supported email provider is Oracle Cloud Infrastructure Email Delivery service.
  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

    See Configure SMTP Connection for more information.

  2. Generate SMTP credentials for Email Delivery. UTL_SMTP uses credentials to authenticate with Email Delivery servers when you send email.

    See Generate SMTP Credentials for a User for more information.

  3. Create an approved sender for Email Delivery. Complete this step for all email addresses you use as the "From" with UTL_SMTP.MAIL.

    See Managing Approved Senders for more information.

  4. Allow SMTP access for ADMIN user by appending an Access Control Entry (ACE).

    For example:

    BEGIN
  -- Allow SMTP access for user ADMIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host => ’www.us.example.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;
/
  5. Create a PL/SQL procedure to send email.

    For example, see the sample code shown in SMTP Send Mail Sample Code.

  6. Send a test email using the PL/SQL procedure you created in step 5.

    For example:

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

Note:

When your Autonomous Database instance is configured with a private endpoint, you can set the ROUTE_OUTBOUND_CONNECTIONS database parameter to 'PRIVATE_ENDPOINT' to specify that all outgoing UTL_SMTP connections are subject to the Autonomous Database instance private endpoint VCN's egress rules. See Enhanced Security for Outbound Connections with Private Endpoints for more information.

See UTL_SMTP for information on UTL_SMTP.

See PL/SQL Packages for UTL_SMTP restrictions with Autonomous Database.

SMTP Send Mail Sample Code

Shows sample code for sending mail with UTL_SMTP on Autonomous Database. 

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

Where: