Consider the legacy "pager duty" structure, where one or more members of a team get paged for an event. The pages are distributed but team members are disconnected, so you may not be aware that someone else is already fixing the problem without a second mechanism such as email or chat.
Slack blends the two, so it's a natural evolution to dispense with traditional pages (Slack will page you with WhatsApp-style messages anyway and can be customized) and integrate database messaging directly into Slack.
The process for configuring the integration takes less than an hour.
Set up Webhooks integration on Slack
Go to slack.com and sign up for an account if you don't already have one. It's free.Once you're in Slack, create a new channel (or use an existing one) for messaging. I chose to create a private channel called #oracle. A private channel allows you to control membership. A public channel can be joined by anyone in your Slack domain.
With the channel created, click on the Settings gear at the top. You'll get a drop-down dialog; select "Add an app or integration".
This will take you to Slack's app and integration page. In the search box, type "Incoming Webhooks" and choose the Incoming Webhooks integration.
Click on "Add Configuration". This will take you to the configuration options, where you'll add the channel you want to send notifications to.
The next screen will provide your Webhook URL. Treat this like a password—it's the URL you'll use to send information from the database to Slack. This page also gives you basic information on how to send and format JSON to Slack.
You'll have further options to customize your channel and give it meaning on the following page:
Configure an Oracle Wallet and add the Slack Certificate
Slack uses SSL (HTTPS) so you can't just send web requests directly from Oracle. You have to set up a Wallet and import the Slack certificate.First create a Wallet:
mkdir -p /oracle/wallet
orapki wallet create -wallet /oracle/wallet -pwd suPerStr0ngP@ssword -auto_login
Copy the certificate to your database server and note the location. For this deomonstration I added the certificate to /oracle/wallet. You could easily put it in a shared location, particularly if you have multiple database servers that you'll be configuring as it will save you having to copy the certificate to each machine.
Now, add the certificate to the Wallet:
orapki wallet add -wallet /oracle/wallet -trusted_cert -cert "/oracle/wallet/GeoTrustGlobalCA.cer" -pwd suPerStr0ngP@ssword
Send JSON from Oracle to Slack
With the certificate imported into the Wallet, all we need to do now is send a POST request to the Webhooks URL with the JSON message we want to have reported in Slack.
declare
req utl_http.req;
res utl_http.resp;
url varchar2(4000) := 'https://hooks.slack.com/services/your_webhook_url_goes_here';
name varchar2(4000);
buffer varchar2(4000);
content varchar2(4000);
db_name v$database.name%TYPE;
begin
utl_http.set_wallet('file:/oracle/wallet', 'suPerStr0ngP@ssword');
select name
into db_name
from v$database;
content := '{"channel": "#oracle", "username": "orabot", "text": "Test post to the #oracle channel from database ' || db_name || '", "icon_emoji": ":oracle:"}';req := utl_http.begin_request(url, 'POST',' HTTP/1.1');
utl_http.set_header(req, 'user-agent', 'mozilla/4.0');
utl_http.set_header(req, 'content-type', 'application/json');
utl_http.set_header(req, 'Content-Length', length(content));
utl_http.write_text(req, content);
res := utl_http.get_response(req);
begin
loop
utl_http.read_line(res, buffer);
dbms_output.put_line(buffer);
end loop;
utl_http.end_response(res);
exception
when utl_http.end_of_body
then utl_http.end_response(res);
end;
end;
/
The end result is the message being delivered to Slack:
