Send Email on Autonomous Database on Dedicated Exadata Infrastructure

APPLIES TO: Oracle Public Cloud only

You can send email on Autonomous Database on Dedicated Exadata Infrastructure using the Email Delivery Service or the DBMS_CLOUD_NOTIFICATION procedures. This article describes how to send email using both these options.

Related Topics

Notes on the Use of Database PL/SQL Packages

Send Email with Email Delivery Service on Autonomous Database

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

To send email using UTL_SMTP:

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. For example, select smtp.email.us-phoenix-1.oci.oraclecloud.com when US West (Phoenix) is the sending region. See from Configure SMTP Connection for more information.
  
  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.
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.
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.
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 Send Email Sample Code.
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 Send Email Sample Code.

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

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.

SMTP Send Email Sample Code

Shows sample code for sending email 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;
/

Send Messages as Email from Autonomous Database

You can use the DBMS_CLOUD_NOTIFICATION to send messages as an email.

Note:

You can only use DBMS_CLOUD_NOTIFICATION for Email notifications with Autonomous Database version 19.21 and above.

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

Create a credential object and use DBMS_CLOUD_NOTIFICATION.SEND_MESSAGE to send a message as an email.

BEGIN
   DBMS_CLOUD.CREATE_CREDENTIAL(
        credential_name => 'EMAIL_CRED',
        username        => 'username',
        password        => 'password'
   );
   END;
/
BEGIN
   DBMS_CLOUD_NOTIFICATION.SEND_MESSAGE(
        provider        => 'email',
        credential_name => 'EMAIL_CRED',
        message         => 'Subject content',
        params          => json_object('recipient' value  'mark@example.com, suresh@example.com',
                                       'to_cc'  value 'nicole@example.com, jordan@example.com',
                                       'to_bcc' value 'manisha@example.com',
                                       'subject' value 'Test subject',
                                       'smtp_host' value 'smtp.email.example.com',
                                       'sender'    value  'approver_sender@example.com' )
   );
   END;
/

Use the params parameter to specify the sender, smtp_host, subject, recipient, and recipients of a CC or BCC in string values.

sender: specifies the Email ID of the approved sender from Step 3.
smtp_host: specifies the SMTP host name from step 2.
subject: specifies the subject of the email.
recipient: specifies the email IDs of recipients. Use a comma between email IDs when there are multiple recipients.
to_cc: specifies the email IDs that are receiving a CC of the email. Use a comma between email IDs when there are multiple CC recipients.
to_bcc: specifies the email IDs that are receiving a BCC of the email. Use a comma between email IDs when there are multiple BCC recipients.

See SEND_MESSAGE Procedure for more information.

Send Query Results as Email from Autonomous Database

You can use the DBMS_CLOUD_NOTIFICATION package to send the results of a query as an email.

Note:

You can only use DBMS_CLOUD_NOTIFICATION for Email notifications with Autonomous Database version 19.21 and above.

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.
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.
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.
Create a credential object and use DBMS_CLOUD_NOTIFICATION.SEND_DATA to send the output of a query as an email.
```
BEGIN
   DBMS_CLOUD.CREATE_CREDENTIAL(
        credential_name => 'EMAIL_CRED',
        username        => 'username',
        password        => 'password'
   );
   END;
/
BEGIN
   DBMS_CLOUD_NOTIFICATION.SEND_DATA(
        provider        => 'email',
        credential_name => 'EMAIL_CRED',
        query           => 'SELECT tablespace_name FROM dba_tablespaces',
        params          => json_object('recipient' value  'mark@example.com, suresh@example.com',
                                       'to_cc'  value 'nicole@example.com1, jordan@example.com',
                                       'to_bcc' value 'manisha@example.com',
                                       'subject' value 'Test subject',
                                       'type' value 'json',
                                       'title' value 'mytitle',
                                       'message' value 'This is the message',
                                       'smtp_host' value 'smtp.email.example.com',
                                       'sender'    value  'approver_sender@example.com' )
   );
   END;
/
```
Use the params parameter to specify the sender, smtp_host, subject, recipient, recipients of a CC or BCC, the message, data type, and the title in String values.
- sender: specifies the Email ID of the approved sender from Step 3.
- smtp_host: specifies the SMTP host name from step 2.
- subject: specifies the subject of the email. The maximum size is 100 characters.
- recipient: This specifies the email IDs of recipients. Use a comma between email IDs when there are multiple recipients.
- to_cc: specifies the email IDs that are receiving a CC of the email. Use a comma between email IDs when there are multiple CC recipients.
- to_bcc: specifies the email IDs that are receiving a BCC of the email. Use a comma between email IDs when there are multiple BCC recipients.
- message: specifies the message text.
- type: specifies the output format as either CSV or JSON.
- title: specifies the title of the attachment of SQL output. The title should only contain letters, digits, underscores, hyphens, or dots as characters in its value due to it being used to generate a file name.
See SEND_DATA Procedure for more information.

Title and Copyright Information

Send Email on Autonomous Database on Dedicated Exadata Infrastructure

Documentation Accessibility

For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.

Access to Oracle Support

Oracle customers that have purchased support have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are hearing impaired.