Wednesday, February 21, 2018

Using the Slack webhook API from PL/SQL

Slack is a web-based chat room popular with many companies. Slack also has an API that can be used to post messages to a given "channel" or chat room. The simplest API offered is the "webhook" integration, which is "a simple way to post messages from external sources into Slack. They make use of normal HTTP requests with a JSON payload that includes the message text and some options."



To create a new Slack webhook, login to Slack and go to "Apps" in the left sidebar menu. Click "Manage apps..." and click "Custom Integrations" and then "Incoming Webhooks". Click on "Add Configuration" to create a new webhook. Specify the channel the webhook will post in, and click "Add incoming webhooks integration".


Next, take a note of the Webhook URL:



Then go to the Alexandria PL/SQL Utility Library and install the SLACK_UTIL_PKG package in your database schema. Modify your database Network ACL settings to include "hooks.slack.com" (port 443) to allow connections to the Slack site from the database.

Then use the package like this (see also demo script):



In addition to application-specific messages you could perhaps also set up the database (via a background job) to notify you when you are about to run out of disk space, when the number of APEX page views reach a certain threshold, when a specific user logs in, and so on. Use your imagination! :-)





2 comments:

Edwin van Meerendonk said...

Nicely packaged. Did you also look at building a slack-app that communicates with the database? It is quite easy to create an app in slack to implement slash-commands that call webservices, served by ORDS e.g.

In this way, you can have two-way communication from slack with your database.

E.g. I let people query some aspects of their Apex application trough their phone using Slack.

It's such a nice platform

Morten Braten said...

@Edwin: Thanks for the feedback, those are some good ideas! I went and read about the Commands API (https://api.slack.com/slash-commands) and as you point out, one can certainly use it to trigger various actions in the database and/or respond with information from the database.

One could create a PL/SQL procedure with the parameters listed in the Commands API and expose it as a URL, or set up a handler using ORDS.

That said, the actual implementation of such handlers would be quite application-specific, so I don't know how much of this belongs in a general-purpose utility library such as Alexandria.

- Morten