Oracle Cloud Infrastructure Documentation

Send Email on Autonomous Database

There are a number of options for sending email on Autonomous Database. You can also send text messages or the output of an SQL query to a Slack or MSTeams channel.

Parent topic: Send Email and Notifications on Autonomous Database

Send Email with Email Delivery Service on Autonomous Database

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

Note

Oracle Cloud Infrastructure Email Delivery Service is the only supported email provider for public SMTP endpoints.

To send email with 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
    Note

    If the SMTP connection endpoint is in a different region from your Autonomous Database, then you must create a request at Oracle Cloud Support to have Cloud Operations enable that region's Email Delivery service for your Autonomous Database.

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

See UTL_SMTP for information on UTL_SMTP.

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

Parent topic: Send Email on Autonomous Database

SMTP Send Email Sample Code

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

Send Email with an Email Provider on a Private Endpoint

Describes the steps to send email with an email provider that is on Private Endpoint.

To send email from Autonomous Database using a email provider on a private endpoint, the email provider must be accessible from the Oracle Cloud Infrastructure VCN (the Autonomous Database instance's private endpoint). For example, you can access an email provider when:

  • Both the source Autonomous Database instance and the email provider are in the same Oracle Cloud Infrastructure VCN.

  • The source Autonomous Database instance and the email provider are in different Oracle Cloud Infrastructure VCNs that are paired.

  • The email provider is on an on-premises network that is connected to the source Autonomous Database instance's Oracle Cloud Infrastructure VCN using FastConnect or VPN.

As a prerequisite, to send email using an email provider, define the following ingress and egress rules:

  • Define an egress rule in the source database's subnet security list or network security group such that the traffic to the target host is allowed on port 587 or port 25 (depending on which port you are using).

  • Define an ingress rule in the target host's subnet security list or network security group such that the traffic from the source Autonomous Database instance's IP address to port 587 or port 25 is allowed (depending on which port you are using).

To send email from an email provider on private endpoint:

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

    For example:

    -- Create an Access Control List for the host
BEGIN
   DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
         host => 'www.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),
         private_target => TRUE);
END;
/
    Note

    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE only supports a single hostname for the host parameter (on a private endpoint, using an IP address, a SCAN IP, or a SCAN hostname is not supported).

    If you set ROUTE_OUTBOUND_CONNECTIONS to PRIVATE_ENDPOINT, setting the private_target parameter to TRUE is not required in this API. See Enhanced Security for Outbound Connections with Private Endpoints for more information.

  2. Create a PL/SQL procedure to send email.
  3. Send a test email using the PL/SQL procedure you created in step 2.

    For example:

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

Use Credential Objects to set SMTP Authentication

Describes how to pass a credential objects to UTL_SMTP.SET_CREDENTIAL APIs.

The SET_CREDENTIAL subprogram sends the AUTH command to authenticate to the SMTP server.

The UTL_SMTP.SET_CREDENTIAL subprogram enables you to pass credential objects to set SMTP authentication. Credential objects are schema objects, hence they can be accessed only by privileged users and enable you to configure schema-level privileges to access control the credentials. Passing credential objects is a appropriate and secure way to store and manage username/password/keys for authentication.

The UTL_SMTP.SET_CREDENTIAL subprogram is a secure and convenient alternative to UTL_SMTP.AUTH subprogram.

Example


...
UTL_SMTP.AUTH (l_mail_conn, 'ocid1.user.oc1.username', 'xxxxxxxxxxxx', schemes => 'PLAIN');
...

As shown in the example above, when you invoke AUTH subprogram, you must pass the username/password in clear text as part of PL/SQL formal parameters. You might need to embed the username/password into various PL/SQL automation or cron scripts. Passing clear text passwords is a compliance issue that is addressed in UTL_SMTP.SET_CREDENTIAL subprogram.

See AUTH Function and Procedure for more information.

UTL_SMTP.SET_CREDENTIAL Syntax 

PROCEDURE UTL_SMTP.SET_CREDENTIAL (
    c          IN OUT NOCOPY connection,
    credential IN            VARCHAR2,
    schemes    IN            VARCHAR2 DEFAULT NON_CLEARTEXT_PASSWORD_SCHEMES
);

FUNCTION UTL_SMTP.SET_CREDENTIAL (
    c          IN OUT NOCOPY connection,
    credential IN            VARCHAR2,
    schemes    IN            VARCHAR2 DEFAULT NON_CLEARTEXT_PASSWORD_SCHEMES)
    RETURN reply;

Example

  • Create a credential object:

    BEGIN DBMS_CLOUD.CREATE_CREDENTIAL (
    credential_name => 'HTTP_CRED',
    username        => 'web_app_user',
    password        => '<password>' );
END;

    This creates a credential object which creates a stored username/password pair.

    See CREATE_CREDENTIAL Procedure for more information.

    See Specifying Scheduler Job Credentials for more information.

  • Execute UTL_SMTP.SET_CREDENTIAL procedure: 

    DECLARE
      l_mail_conn UTL_SMTP.CONNECTION;
    BEGIN 
      l_mail_conn := UTL_SMTP.OPEN_CONNECTION('smtp.example.com', 587);
      UTL_SMTP.SET_CREDENTIAL(l_mail_conn, 'SMTP_CRED', SCHEMES => 'PLAIN');
      ...
END;

    This example sends the command to authenticate to the SMTP server. The Web server needs this information to authorize the request. The value l_mail_conn is the SMTP connection, SMTP_CRED is the credentials name and PLAIN is the SMTP authentication scheme.

See UTL_SMTP for more information.

See PL/SQL Packages Notes for Autonomous Database for information on restrictions for UTL_SMTP on Autonomous Database.

Send Email from Autonomous Database Using DBMS_CLOUD_NOTIFICATION

Use the DBMS_CLOUD_NOTIFICATION package to send messages and query results as email.

Note

DBMS_CLOUD_NOTIFICATION supports sending email only to public SMTP endpoints. Oracle Cloud Infrastructure Email Delivery Service is the only supported email provider.

Parent topic: Send Email on Autonomous Database

Send Messages as Email from Autonomous Database

You can use the DBMS_CLOUD_NOTIFICATION to send messages as an email.
  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 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.

To use DBMS_CLOUD_NOTIFICATION to send mail:

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

    Notes for sending the results of a query as mail:

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

    • The maximum message size for use with DBMS_CLOUD_NOTIFICATION.SEND_DATA for mail notification is 32k bytes.

    See SEND_DATA Procedure for more information.