Note:

Use the DBMS_CLOUD_NOTIFICATION package to send an email from Oracle Autonomous Database

Introduction

Oracle Autonomous Database provides an easy-to-use, fully autonomous database that scales elastically and delivers fast query performance. As a service, Autonomous Database does not require database administration.

Oracle Autonomous Database supports the DBMS_CLOUD_NOTIFICATION package to send messages and query results as email. This package supports sending email only to public SMTP endpoints.

Note: The only supported email provider currently is Oracle Cloud Infrastructure (OCI) Email Delivery service.

Objectives

To send messages and output of a query as email from Oracle Autonomous Database using the DBMS_CLOUD_NOTIFICATION package.

Prerequisites

Task 1: Identify and configure SMTP connection

  1. Open the navigation menu and click Developer Services. Under Application Integration, click Email Delivery.

  2. In the Resources menu, click Configuration.

    Configuration

    • Public Endpoint: The public endpoint used to send an email to, for this region.

    • SMTP Ports: The SMTP ports used to accept an email. Email Delivery supports TLS on port 25 or 587.

    • Security: This field indicates if TLS, the standard means of performing encryption in transit for emails, is being used. Customers must encrypt emails while it is in transit to the Oracle Cloud Infrastructure Email Delivery service. Encrypted emails are protected from being read during transit.

Task 2: Generate SMTP credentials for Email Delivery

  1. View the user details:

    • If you are generating SMTP credentials for yourself: Open the Profile menu and click User Settings, or your account name.
    • If you are an administrator generating SMTP credentials for another user: Open the navigation menu and click Identity & Security. Under Identity, click Users. Locate the user in the list, and then click the user name to view the details.

  2. Click SMTP Credentials.

  3. Click Generate SMTP Credentials.

  4. Enter a Description of the SMTP Credentials in the dialog box.

  5. Click Generate SMTP Credentials. A user name and password like below is displayed.

    Credentials

Task 3: Create an approved sender for Email Delivery

Register a sender email address to be used for email delivery.

  1. Open the navigation menu and click Developer Services. Under Application Integration, click Email Delivery. Under Email Delivery, click Approved Senders. Ensure that you are in the correct compartment. Your user must be in a group with permissions to manage approved-senders in this compartment.

  2. On the Approved Senders page, click Approved Sender.

  3. In the Create Approved Sender dialog box, provide the following information:

    • Enter the email address that you want to list as an approved sender.
    • (Optional) Add tags to organize your resource. To apply a defined tag, you must have permissions to use the tag namespace. For more information about tagging, see Resource Tags. If you are not sure whether to apply tags, skip this option (you can apply tags later) or ask your administrator.

  4. Click Create Approved Sender.

  5. The email address is added to your Approved Senders list.

    Sender

Note:

Task 4: Send messages as email from Oracle Autonomous Database

  1. Provision an Oracle Autonomous Database: go to the Details page, and click Database Actions. A sign-in page opens for Database Actions. Use your database instance’s default administrator account, Username - admin, and click Next. The Database Actions page opens. In the Development box, click SQL.

    DB actions

  2. Create a credential object and use DBMS_CLOUD_NOTIFICATION.SEND_MESSAGE to send a message as an email. Copy and paste the code snippet below in your SQL Worksheet.

    BEGIN
   DBMS_CLOUD.CREATE_CREDENTIAL(
        credential_name => 'test',
        username        => 'username',
        password        => '************'
   );
   END;
/
BEGIN
   DBMS_CLOUD_NOTIFICATION.SEND_MESSAGE(
        provider        => 'email',
        credential_name => 'test',
        message         => 'This message is to confirm that you have received an email from Autonomous Database',
        params          => json_object('recipient' value  '************',
                                       'to_cc'  value '************',
                                       'to_bcc' value ‘************’,
                                       'subject' value 'Test email',
                                       'smtp_host' value 'smtp.email.us-phoenix-1.oci.oraclecloud.com',
                                       'sender'    value  'send.email@test.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: Email ID of the approved sender
    • smtp_host: SMTP host name
    • subject: Subject of the email
    • recipient: Email IDs of recipients
    • to_cc: Email IDs that are receiving a CC of the email
    • to_bcc: Email IDs that are receiving a BCC of the email

  3. Make sure you click the Run Statement button to run it in the SQL Worksheet.

    Send message

Once the procedure is successfully completed, you will receive the email.

Email message

Task 5: Send query results as email from Oracle Autonomous Database

  1. You can use the DBMS_CLOUD_NOTIFICATION package to send the results of a query as an email. For example, use DBMS_CLOUD_NOTIFICATION.SEND_MESSAGE to send a message with output of query - SELECT tablespace_name FROM dba_tablespaces; as an email. Copy and paste the code snippet below in your SQL Worksheet.

    BEGIN
   DBMS_CLOUD.CREATE_CREDENTIAL(
        credential_name => 'test',
        username        => ‘username’
        password        => '************'
   );
   END;
/
BEGIN
   DBMS_CLOUD_NOTIFICATION.SEND_DATA(
        provider        => 'email',
        credential_name => 'test',
        query           => 'SELECT tablespace_name FROM dba_tablespaces',
        params          => json_object('recipient' value  '************',
                                       'to_cc'  value '****************',
                                       'to_bcc' value '****************',
                                       'subject' value 'Tablespace Report',
                                       'type' value 'json',
                                       'title' value 'Tablespace report',
                                       'message' value 'Here is the tablespace report',
                                       'smtp_host' value 'smtp.email.us-phoenix-1.oci.oraclecloud.com',
                                       'sender'    value  'send.email@test.com' )
   );
   END;
/

  2. Make sure you click the Run Statement button to run it in the SQL Worksheet.

    Send data

Once the procedure is successfully completed, you will receive the email.

Query email

Next Steps

The DBMS_CLOUD_NOTIFICATION package is now supported in Oracle Autonomous Database and we just saw how to send an email by using this package. If you’d like to learn more about Email Delivery or DBMS_CLOUD_NOTIFICATION in Oracle Autonomous Database, see the Oracle Autonomous Database documentation.

Acknowledgments

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.