DBMS_CLOUD_NOTIFICATION Package

The DBMS_CLOUD_NOTIFICATION package allows you to send messages or the output of a SQL query to a provider.

The supported providers with DBMS_CLOUD_NOTIFICATION are: Email, Microsoft Teams, OCI Notifications (ONS), and Slack.

When you use the OCI Notifications service with DBMS_CLOUD_NOTIFICATION package, only the DBMS_CLOUD_NOTIFICATION.SEND_MESSAGE procedure is supported (the DBMS_CLOUD_NOTIFICATION.SEND_DATA procedure is not supported).

Prerequisites

An outbound connectivity must have been configured using a NAT gateway, by your fleet administrator as described below:

Create a NAT gateway in the Virtual Cloud Network (VCN) where your Autonomous AI Database resources reside by following the instructions in Create a NAT Gateway in Oracle Cloud Infrastructure Documentation.
After creating the NAT gateway, add a route rule and an egress security rule to each subnet (in the VCN) where Autonomous AI Database resources reside so that these resources can use the gateway to obtain a public key from your Azure AD instance:
1. Go to the Subnet Details page for the subnet.
2. In the Subnet Information tab, click the name of the subnet's Route Table to display its Route Table Details page.
3. In the table of existing Route Rules, check whether there is already a rule with the following characteristics:
  - Destination: 0.0.0.0/0
  - Target Type: NAT Gateway
  - Target: The name of the NAT gateway you just created in the VCN
  If such a rule does not exist, click Add Route Rules and add a route rule with these characteristics.
4. Return to the Subnet Details page for the subnet.
5. In the subnet's Security Lists table, click the name of the subnet's security list to display its Security List Details page.
6. In the side menu, under Resources, click Egress Rules.
7. In the table of existing Egress Rules, check whether there is already a rule with the following characteristics:
  - Destination Type:CIDR
  - Destination:0.0.0.0/0
  - IP Protocol:TCP
  - Source Port Range:443
  - Destination Port Range:All
  If such a rule does not exist, click Add Egress Rules and add an egress rule with these characteristics.

The HTTP Proxy settings in your environment must allow the database to access the cloud service provider.

These settings are defined by your fleet administrator while creating the Exadata Cloud@Customer infrastructure as described in Using the Console to Provision Exadata Database Service on Cloud@Customer.

Note: The network configuration including the HTTP Proxy can only be edited until the Exadata Infrastructure is in Requires Activation state. Once it is activated, you cannot edit those settings.

Setting up an HTTP Proxy for an already provisioned Exadata Infrastructure needs a Service Request (SR) in My Oracle Support. See Create a Service Request in My Oracle Support for details.

Summary of DBMS_CLOUD_NOTIFICATION Subprograms

This table summarizes the subprograms included in the package.

Subprogram	Description
SEND_DATA Procedure	Send SQL query output to a provider.
SEND_MESSAGE Procedure	Send a text message to a provider.

SEND_DATA Procedure

The SEND_DATA procedure sends the results of the specified query to a provider.

Syntax

DBMS_CLOUD_NOTIFICATION.SEND_DATA(
       provider           IN  VARCHAR2,
       credential_name    IN  VARCHAR2,
       query              IN  CLOB,
       params             IN  CLOB
 );

Parameters

Parameter	Description
`provider`	Specifies the provider. Valid values are: '`email`', '`msteams`', and '`slack`' This parameter is mandatory.
`credential_name`	The name of the credential object to access the provider. For the email provider, the credential is the name of the credential of the SMTP approved sender that contains its username and password. For the msteams provider, the credential is the name of the credential. For the Slack provider, the credential's username must be `"SLACK_TOKEN"` and the `password` is the Slack Bot Token. This parameter is mandatory.
`query`	Specifies the SQL query to run. Results are sent to the provider. This parameter is mandatory.
`params`	Specifies specific parameters for the `provider` in JSON format. For the provider type `email`, the valid parameters are: `sender`. This specifies the Email ID of the approved sender in a `String` value. `smtp_host`. This specifies the SMTP host name in a `String` value. `subject`. This specifies the subject of the email in a `String` value The maximum size is 100 characters. `recipient`. This specifies the email IDs of recipients in a `String` value. Use a comma between email IDs when there are multiple recipients. `to_cc`. This specifies the email IDs that are receiving a CC of the email. It is a `String` value. Use a comma between email IDs when there are multiple CC recipients. `to_bcc`. This specifies the email IDs that are receiving a BCC of the email. It is a `String` value. Use a comma between email IDs when there are multiple BCC recipients. `message`. This specifies the message text in a `String` value. `type`. This specifies the output format in a `String` value as either CSV or JSON. `title`. This specifies the title of the attachment of SQL output in a `String` value. 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. For the provider type `msteams`, the valid parameters are: `tenant`. This specifies the tenant ID in a `String` format. `team`. This specifies the team ID in a `String` format. `channel`. This specifies the channel ID in a `String` format. `title`. This specifies the title of the file in a `String` format. The maximum size is 50 characters. `type`. This specifies the output format in a `String` value. Valid values are CSV or JSON. For the provider type `slack`, the valid parameters are: `channel`. This specifies the Channel ID in a `String` value. The Channel ID is a unique ID for a channel and is different from the channel name. In Slack, when you view channel details you can find the Channel ID on the "About" tab. `type`. This specifies the output format in a `String` value. Valid values are CSV or JSON. This parameter is mandatory.

Usage Notes

Use the procedure DBMS_CLOUD.CREATE_CREDENTIAL(credential_name, username, password) to create the credential object. See CREATE_CREDENTIAL Procedure for more information.

For the email provider, the user requires an SMTP connection endpoint for the Email Delivery server to obtain smtp_host. The user also requires an SMTP approved sender and its credentials to authenticate with the Email Delivery server to obtain the credential_name. SMTP connection must be configured, and SMTP credentials must be generated and approved.
For the msteams provider, the user requires the Microsoft Teams App and a bot configured in it. The app needs to be published to the org and installed after obtaining approval from the admin from the admin center. The user also requires access for Files.ReadWrite.All and ChannelSettings.Read.All permission for Graph API from their Azure Portal. To generate the required token, the user requires bot_id in the username and bot_secret in the password. The maximum file size supported when using DBMS_CLOUD_NOTIFICATION.SEND_DATA for Microsoft Teams is 4MB.
For the slack provider, the username value can be any valid string and the password is the Slack bot token.

Example

Send SQL output with the email provider:

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@oracle.com, suresh@oracle.com',
                                       'to_cc'  value 'nicole@oracle.com1, jordan@oracle.com',
                                       'to_bcc' value 'manisha@oracle.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@oracle.com' )
   );
   END;
/

Send SQL output with the msteams provider:

BEGIN
    DBMS_CLOUD.CREATE_CREDENTIAL(credential_name => 'TEAMS_CRED',
        username        => '*bot_id*',
        password        => '*bot_secret*');
END;
/

BEGIN
     DBMS_CLOUD_NOTIFICATION.SEND_MESSAGE(provider => 'msteams',
        credential_name => 'TEAMS_CRED',
        query           => 'SELECT tablespace_name FROM dba_tablespaces',
        params          => json_object('tenant'value '5b743bc******c0286',
                                       'team'value '0ae401*********5d2bd',
                                       'channel'value '19%3a94be023*****%40thread.tacv2',
                                       'title'value 'today',
                                       'type'value 'csv'));
END;
/

Send SQL output with the slack provider:

BEGIN
   DBMS_CLOUD.CREATE_CREDENTIAL(
     credential_name => 'SLACK_CRED',
     username        => 'SLACK_TOKEN',
     password        => 'password');
END;
/
BEGIN
  DBMS_CLOUD_NOTIFICATION.SEND_DATA(
      provider        => 'slack',
      credential_name => 'SLACK_CRED',
      query           => 'SELECT username, account_status, expiry_date FROM USER_USERS WHERE rownum < 5',
      params          => json_object('channel' value 'C0....08','type' value 'csv'));
END;
/

SEND_MESSAGE Procedure

The procedure sends a text message to a provider.

Syntax

DBMS_CLOUD_NOTIFICATION.SEND_MESSAGE(
       provider           IN  VARCHAR2,
       credential_name    IN  VARCHAR2,
       message            IN  CLOB,
       params             IN  CLOB
 );

Parameters

Parameter	Description
`provider`	Specifies the provider. Valid values are: '`email`', '`msteams`', '`slack`', and '`oci`'. This parameter is mandatory.
`credential_name`	The name of the credential object to access the provider. For the email provider, the credential is the name of the credential of the SMTP approved sender that contains its username and password. For the msteams provider, the credential must contain the `bot_id` and the `bot_secret` key in both the username and the password. For the Slack provider, the credential's username must be `"SLACK_TOKEN"` and the `password` is the Slack Bot Token. For the `oci` provider, the credential is a valid Oracle Cloud Infrastructure signing key based credential. This parameter is mandatory.
`message`	Specifies the message text. This parameter is mandatory.
`params`	Specifies specific parameters for the `provider` in JSON format. For the provider type `email`, the valid parameters are: `sender`. This specifies the Email ID of the approved sender in a `String` value. `smtp_host`. This specifies the SMTP host name in a `String` value. `subject`. This specifies the subject of the email in a `String` value. The maximum size is 100 characters. `recipient`. This specifies the email IDs of recipients in a `String` value. Use a comma between email IDs when there are multiple recipients. `to_cc`. This 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`. This specifies the email IDs that are receiving a BCC of the email. Use a comma between email IDs when there are multiple BCC recipients. For the provider type `msteams`, the valid parameter is: `channel`. This specifies the Channel ID in a `String` value. For the provider type `slack`, the valid parameter is: `channel`. This specifies the Channel ID in a `String` value. For the provider OCI Notifications with the type `oci`, the valid parameters are: `topic_ocid`: Specifies `title`: Specifies for the subject line of the message. With the `oci` provider, there are different possible endpoints for a message, based on the subscriptions. The `title` parameter is treated differently for different subscriptions: email: The `title` specifies the subject line of the message. Slack: The `title` value is not used. If it is included the value is ignored. The Channel ID is a unique ID for a channel and is different from the channel name. In Slack, when you view channel details you can find the Channel ID on the "About" tab. This parameter is mandatory.

Usage Notes

Use the procedure DBMS_CLOUD.CREATE_CREDENTIAL(credential_name, username, password) to create the credential object. See CREATE_CREDENTIAL Procedure for more information.

For the email provider, the user requires an SMTP connection endpoint for the Email Delivery server to obtain smtp_host. The user also requires an SMTP approved sender and its credentials to authenticate with the Email Delivery server to obtain the credential_name. SMTP connection must be configured, and SMTP credentials must be generated and approved.
For the msteams provider, the user requires the Microsoft Teams App and a bot configured in it. The app needs to be published to the org and installed after obtaining approval from the admin from the admin center. The user also requires access for Files.ReadWrite.All and ChannelSettings.Read.All permission for Graph API from their Azure Portal. To generate the required token, the user requires bot_id in the username and bot_secret in the password.
For the slack provider, the username value can be any valid string and the password is the Slack bot token.
The DBMS_CLOUD_NOTIFICATION supports the OCI Notifications provider. See Overview of Notifications for details on the OCI Notifications provider.
- To use the OCI Notifications provider, you must do the following:
  - Create a topic. See Creating a Topic for details on creating a topic
  - Create the required subscriptions. See Creating a Subscription for details on creating a subscription.
  - After you create the subscriptions, confirm the subscriptions. Messages are only published to confirmed subscriptions. See Confirming a Subscription for details on confirming subscriptions.
- The Autonomous AI Database instance, the credential (if you are using user credentials), and the subscription topic must all be located in the same region.
- Note the following limitations:
  - Message Size Limit: The message size limit per request is 64KB. Messages exceeding this size cannot be sent or processed within a single request.
  - Message Delivery Rate Limit: There are different message delivery rate limits depending on the protocol used.
    - email protocol: the limit is 10 messages per minute per endpoint.
    This restriction may impact the speed at which messages can be transmitted and received.
  - Transactions Per Minute (TPM) per-Tenancy Limit: There is a TPM limit of 60 per topic for this operation. This limit represents the maximum number of messages per minute that can be processed or handled for a given topic. Exceeding this limit may result in messages not being processed or processed at a slower rate.

Examples

Send a text message with the email provider:

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@oracle.com, suresh@oracle.com',
                                       'to_cc'  value 'nicole@oracle.com, jordan@oracle.com',
                                       'to_bcc' value 'manisha@oracle.com',
                                       'subject' value 'Test subject',
                                       'smtp_host' value 'smtp.email.example.com',
                                       'sender'    value  'approver_sender@oracle.com' )
   );
   END;
/

Send a text message with the msteams provider:

BEGIN
    DBMS_CLOUD.CREATE_CREDENTIAL(credential_name => 'TEAMS_CRED',
        username        => '*bot_id*',
        password        => '*bot_secret*');
END;
/

BEGIN
     DBMS_CLOUD_NOTIFICATION.SEND_MESSAGE(
        provider        => 'msteams',
        credential_name => 'TEAMS_CRED',
        message         => 'text from new teams api',
        params          => json_object('channel'value 'C0....08'),'region'value 'india');
END;
/

Send a text message with the slack provider:

BEGIN
   DBMS_CLOUD.CREATE_CREDENTIAL(
     credential_name => 'SLACK_CRED',
     username        => 'SLACK_TOKEN',
     password        => 'password');
END;
/
BEGIN
   DBMS_CLOUD_NOTIFICATION.SEND_MESSAGE(
      provider          => 'slack',
      credential_name   => 'SLACK_CRED',
      message           => 'Send text from Autonomous AI Database.',
      params            => json_object('channel' value 'C0....08'));
END;
/

Send a text message with the OCI Notifications, type oci provider:

BEGIN
   DBMS_CLOUD_NOTIFICATION.SEND_MESSAGE(
      provider          => 'oci',
      credential_name  => 'OCI_CRED',
      message          => 'Text message for you.',
      params           => json_object('topic_ocid'  value 'oci********pa',
      'title'  value 'Title for message subject' ));
END;
/