Send Mail on Autonomous Database on Dedicated Exadata Infrastructure

This article describes the steps to send email using UTL_SMTP on Autonomous Database on Dedicated Exadata Infrastructure.

To send email using UTL_SMTP:
  1. Identify your SMTP connection endpoint for Email Delivery.
    1. If you want to use Oracle Cloud Infrastructure (OCI) Email Delivery Service, identify your connection endpoint for Email Delivery from SMTP Connection Endpoints.
      For example, select smtp.email.us-phoenix-1.oci.oraclecloud.com when US West (Phoenix) is the sending region.

      Note:

      You may need to subscribe to additional Oracle Cloud Infrastructure regions if Email Delivery is unavailable in your current region.
    2. If you want to use a third-party SMTP server, your network administrator must configure the SMTP server and its connectivity with the Autonomous Database tenant and provide the SMTP connection endpoint information.
  2. Generate SMTP credentials for Email Delivery for the OCI user.
    UTL_SMTP uses credentials to authenticate with Email Delivery servers when you send an email. See Generate SMTP Credentials for a User for more information.
  3. Create approved senders for Email Delivery.
    If you are using OCI Email Delivery Service, 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 host access for the user(s) with administrator role by appending Access Control Entry (ACE).
    To use secure SMTP, you must set up the database user with Access Control Entry (ACE) for wallet access, in addition to SMTP host access. For secure SMTP access using OCI Email Delivery Service, the Oracle Cloud Infrastructure account must be authenticated using Simple Authentication and Security Layer (SASL) protocols such as PLAIN, CRAM-MD5, LOGIN. For example, see the sample code in SMTP Mail Sample Code.
  5. Write the PL/SQL code to send email using UTL_MAIL.

    Tip:

    If you are developing the send email procedure by yourself, ensure that your code is according to SMTP Mail Sample Code.

See UTL_SMTP for further reference.

See Notes on the Use of Database PL/SQL Packages for more information about using UTL_SMTP with Autonomous Database.

Related Topics

SMTP Send Mail Sample Code

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

Sample Code

-- As ADMIN user, set up ACE to access SMTP host
connect admin/[password]@[ADBD-host]/[pdbname]_tp.atp.oraclecloud.com
begin
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE (
    host       => '[SMTP_Server_address]',
    lower_port => 25,
    upper_port => 25,
    ace        => xs$ace_type(
                    privilege_list => xs$name_list('smtp'),
                    principal_name => '[database_user]',
                    principal_type => xs_acl.ptype_db));
end;

/
-- If using secure SMTP, set up ACE to wallet for certificate-based secure (TLS) email
declare
  m_wallet_path VARCHAR2(512);
begin
  select property_value into m_wallet_path
    from database_properties
   where property_name like '%SSL%';
  m_wallet_path := 'file:' || m_wallet_path;
  DBMS_NETWORK_ACL_ADMIN.APPEND_WALLET_ACE (
    wallet_path => m_wallet_path,
    ace         => xs$ace_type(
                     privilege_list => xs$name_list('use_client_certificates'),
                     principal_name => '[database_user]',
                     principal_type => xs_acl.ptype_db));
end;
/
-- As ADMIN or regular database user with privileges set up above, use UTL_SMTP to send email
connect [database_user]/[password]@[ADBD-host]/[pdbname]_tp.atp.oraclecloud.com

-- Example of normal (non-secure) email
DECLARE
  c         UTL_SMTP.CONNECTION;
  reply     UTL_SMTP.REPLY;
  host      varchar2(64) := '[SMTP_Server_address]',
  port      pls_integer := 25;
  sender    varchar2(64) := '[sender-address@domain.com]';
  recipient varchar2(64) := '[recipient-address@domain.com]';
BEGIN
  c := UTL_SMTP.OPEN_CONNECTION (host, port);
  if (reply.CODE != 220) then
    raise_application_error(-20000,'UTL_SMTP.CONNECTION: '
      || reply.CODE || ' - ' || reply.TEXT);
  end if;

  UTL_SMTP.HELO(c, host);
  UTL_SMTP.MAIL(c, sender);
  UTL_SMTP.RCPT(c, recipient);
  UTL_SMTP.OPEN_DATA(c);
  UTL_SMTP.WRITE_DATA(c,'From:[Sender]  <' || sender || '>'    || UTL_TCP.CRLF);
  UTL_SMTP.WRITE_DATA(c,'To:[Recipient] <' || recipient || '>' || UTL_TCP.CRLF);
  UTL_SMTP.WRITE_DATA(c,'Subject: Email from UTL_SMTP App' || UTL_TCP.CRLF);
  UTL_SMTP.WRITE_DATA(c,'Mail Content');
  UTL_SMTP.CLOSE_DATA(c);
  dbms_output.put_line('Close Data: ' || reply.code);
  UTL_SMTP.QUIT(c);
END;
/

-- Example of secure email using OCI Mail Server as the SMTP server
DECLARE
  m_wallet_path VARCHAR2(256);
  c             UTL_SMTP.CONNECTION;
  reply         UTL_SMTP.REPLY;
  host          varchar2(64) := 'smtp.email.[region].oci.oraclecloud.com';
  port          pls_integer := 25;
  sender        varchar2(64) := '[sender-address@domain.com]';
  recipient     varchar2(64) := '[recipient-address@domain.com]';
BEGIN
  select property_value into m_wallet_path
    from database_properties
   where property_name like '%SSL%';
  m_wallet_path := 'file:' || m_wallet_path;

  -- secure connection with SSL/TLS made *before* SMTP communication
  -- use hostname matched against CN or SMTP server's certificate
  reply := UTL_SMTP.OPEN_CONNECTION (
             host                          => host,
             port                          => port,
             c                             => c,
             wallet_path                   => m_wallet_path,
             wallet_password               => NULL,
             secure_connection_before_smtp => FALSE,
             secure_host                   => NULL);
  if (reply.CODE != 220) then
      raise_application_error(-20000,'UTL_SMTP.OPEN_CONNECTION: '
        || reply.CODE ||' - ' || reply.TEXT);
  end if;

  UTL_SMTP.STARTTLS(c);
 
  -- username is the 'SMTP credential' for the user's email address
  -- obtained from OCI Identity -> Users -> email address.
  -- Scheme refers to SASL (Simple Authentication and Security Layer)
  -- Authentication Schemes for SMTP protocol - PLAIN, CRAM-MD5, LOGIN
  -- https://docs.oracle.com/en-us/iaas/Content/Email/Concepts/overview.htm
  UTL_SMTP.AUTH(
     c        => c,
     username => '[ocid1.user.verylongaddresscreatedinOCIforuseraddress.com]',
     password => '[password-provided-from-create]',
     schemes  => 'PLAIN');

  UTL_SMTP.HELO(c, host);
  UTL_SMTP.MAIL(c, sender);
  UTL_SMTP.RCPT(c, recipient);
  UTL_SMTP.OPEN_DATA(c);
  UTL_SMTP.WRITE_DATA(c, 'From: Sender <' || sender || '>' || UTL_TCP.CRLF);
  UTL_SMTP.WRITE_DATA(c, 'To: Recipient <' || recipient || '>' || UTL_TCP.CRLF);
  UTL_SMTP.WRITE_DATA(c, 'Subject: Email from UTL_SMTP App' || UTL_TCP.CRLF);
  UTL_SMTP.WRITE_DATA(c, 'Mail Content');
  UTL_SMTP.WRITE_DATA(c, 'Sender is a domain-owned approved sender in OCI Mail Service');
  UTL_SMTP.CLOSE_DATA(c);
  dbms_output.put_line('Close Data: ' || reply.code);
  UTL_SMTP.QUIT(c);
END;
/