Send Slack Notifications from Autonomous Database on Dedicated Exadata Infrastructure

You can configure Slack to send messages, alerts, or output of a query from Autonomous Database on Dedicated Exadata Infrastructure to a Slack channel using the DBMS_CLOUD_NOTIFICATION package.

Related Topics

Prepare to Send Slack Notifications from Autonomous Database

To send Slack notifications, you must configure your Slack application to receive messages from Autonomous Database. Next, create a credential to use with the DBMS_CLOUD_NOTIFICATION procedures to send Slack notifications from Autonomous Database.

To use Slack with DBMS_CLOUD_NOTIFICATION procedures:

  1. Create your Slack app.

    The Slack app is installed in a Slack Workspace, which in turn has Channels where messages can be sent. The bot token of the Slack app must have the following permission scopes defined:

    channels:read
chat:write
files:write

    See Creating an app for information on setting up a Slack app.

  2. Have your Slack admin add the Slack app to the channels to which DBMS_CLOUD_NOTIFICATION can send message through the "Integrations" option in the channel.

    See Basic app setup for more information.

  3. Locate the app's bot token available in the Slack app specific page, under https://app.slack.com.

    See Basic app setup for more information.

  4. On Exadata Cloud@Customer, configure the HTTP Proxy settings in your environment to allow the database to access Slack. See Using the Console to Provision Exadata Database Service on Cloud@Customer for information on setting up the HTTP Proxy settings while creating an Exadata Cloud@Customer Infrastructure.

    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.

    To set up an HTTP Proxy for an already provisioned Exadata Infrastructure, please submit a Service Request (SR) in My Oracle Support. See Create a Service Request in My Oracle Support for details.

  5. Create a credential object to access the Slack app from Autonomous Database.

    Tip:

    If you can not use the CREATE_CREDENTIAL procedure successfully, consult with the ADMIN user to grant execute access on DBMS_CLOUD packages.

    The credential's username is SLACK_TOKEN and the password is the bot token.

    For example:

    BEGIN
   DBMS_CLOUD.CREATE_CREDENTIAL(
     credential_name => 'SLACK_CRED',
     username    => 'SLACK_TOKEN',
     password    => 'xoxb-34....96-34....52-zW....cy');
END;
/

    See CREATE_CREDENTIAL Procedure for more information.

  6. Configure access control to allow user access to external network services (Slack).
    BEGIN
 DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE (
  host         => 'slack.com', 
  lower_port   => 443,
  upper_port   => 443,
  ace          => xs$ace_type(
      privilege_list => xs$name_list('http'),
      principal_name => example_invoking_user, 
      principal_type => xs_acl.ptype_db)); 
END;

    See Configuring Access Control for External Network Services for more information.

Send Messages to a Slack Channel

After creating the Slack credential object as described in Prepare to Send Slack Notifications from Autonomous Database, you can use the DBMS_CLOUD_NOTIFICATION.SEND_MESSAGE procedure to send a message to a Slack channel.
Example:
BEGIN
   DBMS_CLOUD_NOTIFICATION.SEND_MESSAGE(
      provider          => 'slack',
      credential_name   => 'SLACK_CRED',
      message           => 'Alert from Autonomous Database...',
      params            => json_object('channel' value 'C0....08'));
END;
/

Use the params parameter to specify the Slack channel.

  • channel: Specifies the Channel ID.

    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. See How to Find your Slack Team ID and Slack Channel ID for more information.

See SEND_MESSAGE Procedure for more information.

Send Query Results to a Slack Channel

After creating the Slack credential object as described in Prepare to Send Slack Notifications from Autonomous Database, you can use the DBMS_CLOUD_NOTIFICATION.SEND_DATA procedure to send the output of a query to a Slack channel.
Example:
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;
/

Use the params parameter to specify the Slack channel and the data type:

  • channel: Specifies the Channel ID.

    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. See How to Find your Slack Team ID and Slack Channel ID for more information.

  • type: Specifies the output type. Valid values are: 'csv' or 'json'.

See SEND_DATA Procedure for more information.