DBMS_CLOUD_NOTIFICATION Package
The
DBMS_CLOUD_NOTIFICATION
package allows you to send messages or the output of a SQL query to a provider.
- DBMS_CLOUD_NOTIFICATION Overview
This scenario describes setup and use of theDBMS_CLOUD_NOTIFICATION
package. - Summary of DBMS_CLOUD_NOTIFICATION Subprograms
This table summarizes the subprograms included in the package.
Parent topic: Autonomous Database Supplied Package Reference
DBMS_CLOUD_NOTIFICATION Overview
DBMS_CLOUD_NOTIFICATION
package.
The supported providers are: Email, Microsoft Teams, and Slack.
Parent topic: DBMS_CLOUD_NOTIFICATION Package
Summary of DBMS_CLOUD_NOTIFICATION Subprograms
This table summarizes the subprograms included in the package.
Subprogram | Description |
---|---|
Send SQL query output to a provider. |
|
Send a text message to a provider. |
- SEND_DATA Procedure
TheSEND_DATA
procedure sends the results of the specified query to a provider. - SEND_MESSAGE Procedure
The procedure sends a text message to a provider.
Parent topic: DBMS_CLOUD_NOTIFICATION Package
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 |
---|---|
|
Specifies the provider. Valid values are: ' This parameter is mandatory. |
|
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 This parameter is mandatory. |
|
Specifies the SQL query to run. Results are sent to the provider. This parameter is mandatory. |
|
Specifies specific parameters for the For the provider type
For the provider type
For the provider type
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, 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 thecredential_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 theemail
provider is 32k bytes. -
You can only use
DBMS_CLOUD_NOTIFICATION
for mail notifications with Autonomous 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 forFiles.ReadWrite.All
andChannelSettings.Read.All
permission for Graph API from their Azure Portal. To generate the required token, the user requiresbot_id
in the username andbot_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, theusername
value can be any valid string and thepassword
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;
/
Parent topic: Summary of DBMS_CLOUD_NOTIFICATION Subprograms
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 |
---|---|
|
Specifies the provider. Valid values are: ' This parameter is mandatory. |
|
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 For the Slack provider, the credential's username
must be This parameter is mandatory. |
|
Specifies the message text. This parameter is mandatory. |
|
Specifies specific parameters for the For the provider type
email , the valid parameters are:
For the provider type
msteams , the valid parameter is:
For the provider type
slack , the valid parameter is:
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 obtainsmtp_host
. The user also requires an SMTP approved sender and its credentials to authenticate with the Email Delivery server to obtain thecredential_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 forFiles.ReadWrite.All
andChannelSettings.Read.All
permission for Graph API from their Azure Portal. To generate the required token, the user requiresbot_id
in the username andbot_secret
in the password. - For the
slack
provider, theusername
value can be any valid string and thepassword
is the Slack bot token.
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 Database.',
params => json_object('channel' value 'C0....08'));
END;
/
Parent topic: Summary of DBMS_CLOUD_NOTIFICATION Subprograms