Ever heard from Slack.com? Slack is the (not “a” but “the”) platform for team communication. It’s a sort of mixture of email, messaging and a lot more. Well, just go to their website. They are a lot better in explaining their own product of course.
For me it got interesting when I saw their API.
I came up with the idea of setting up a channel within our team and let our database post messages on this channel when some triggering event happen.
Now I get whatsapp-like messages on my mobile phone, tablets and desktops from our database. These are the things that makes me very happy!
This is how I did it:
Step 1 – Set up your company
Just go to Slack.com and follow the wizard. It is very self-explaining.
Just look at the wealth of services they offer. Scroll down to the “DIY Integrations & Customizations” and add a Slack API.
You will be directed to https://api.slack.com/. Just read it.
For now go to the section “Authentication” and add a token.
This token is what you need to authenticate the database as a client. (Yes, it will authenticate itself as you).
Step 4 – Get the certificate(s)
The API I use is a https-call that will be implemented in PL/SQL. For this we will need to store some certificates in an Oracle Wallet.
I use chrome as a browser. Other browsers have comparable functionality.
- Go to https://api.slack.com/
- Right-click the green lock and go to the certificates. This one is in Dutch but you will get the picture.
- Export the GeoTrust Global CA and the GeoTrust SSL CA-G2 certificates to file as Base64 encoded x.509 (*.cer)
You don’t need the leaf certificate *.slack.com
Step 5 – Create a wallet
Using the Oracle Wallet Manager open your wallet or create a new wallet. You don’t need a certificate request.
Under “Operations” choose Import Trusted certificate and import both *.cer files we created before.
Save the wallet to e.g. E:\oracle\Wallet, open the Wallet menu again and enable Auto login. If you don’t do this you will have to embed the password of the wallet in your PL/SQL.
But what if you are using Oracle XE and you don’t have Wallet manager?
Than it’s good to know that the wallet is “just” an PKCS#12 file. Take a look at this excellent post https://blog.hazrulnizam.com/openssl-workaround-oracle-xe-wallet/ that explains how to use Openssl for creating the
openssl pkcs12 -export -in slack.cer -out ewallet.p12 - nokeys
and place this wallet in a directory. E.g. C:\Oracle\Wallet ( remember your password in this case. You will need it when you set your wallet in PL/SQL
Step 6 – Create a procedure in the database
The following PL/SQL implements the chat.postmessage API from slack to send a message to the #notifications channel. It uses the wallet in E:\oracle\Wallet with autologin enabled:
declare p_url varchar2(2000):= 'https://slack.com/api/chat.postMessage'; l_http_request utl_http.req; l_http_response utl_http.resp; l_text varchar2(32767); l_token varchar2(1000) := 'thetokenfromtheslackapisite'; l_param varchar2(32676) := 'channel=#notifications'||chr(38) ||'text=Hello world from the database'; BEGIN utl_http.set_detailed_excp_support ( true ); utl_http.set_wallet('file:E:\oracle\wallet'); -- l_http_request := utl_http.begin_request ( url=>p_url||'?token='||token , method => 'POST' ); utl_http.set_header ( r => l_http_request , name => 'Content-Type' , value => 'application/x-www-form-urlencoded' ); utl_http.set_header ( r => l_http_request , name => 'Content-Length' , value => length(l_param) ); utl_http.write_text ( r => l_http_request , data => l_param ); -- l_http_response := UTL_HTTP.get_response(l_http_request); BEGIN LOOP UTL_HTTP.read_text(l_http_response, l_text, 32766); DBMS_OUTPUT.put_line (l_text); END LOOP; EXCEPTION WHEN utl_http.end_of_body THEN utl_http.end_response(l_http_response); END; EXCEPTION WHEN OTHERS THEN utl_http.end_response(l_http_response); RAISE; END;