DBMS_CLOUD_NOTIFICATION Package

Syntax

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

Parameters

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, note the following:

  • 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. The SMTP connection must be configured and the SMTP credentials must be generated and approved.

  • The maximum message size supported when using DBMS_CLOUD_NOTIFICATION.SEND_DATA with the email provider is 32k bytes.

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

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

Examples

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;
/ 

Syntax

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

Parameters

Usage Note for Email Provider

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.

Usage Note for the Msteams Provider

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.

Usage Note for the Slack Provider

For the slack provider, the username value can be any valid string and the password is the Slack bot token.

Usage Notes for the OCI Provider OCI Notifications

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 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;
/ 

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