Notifications from ADW to OCI via PLSQL

Another post on the most awesome PLSQL SDK for OCI within the Oracle Autonomous Databases. How can I do this or that after this PLSQL/SQL is complete? How can I get an email? How can I run some other code and so on and so on… It’s possible to trigger this from within the database itself and publish a message to OCI notification.

It’s easy with a little code of course, here’s a snippet, the title and notification body are hard-coded here for illustration…

I already have a topic created that I will use in the example, I have subscribed to the topic using email.

I can now call this from PLSQL (credential is either a user credential created with DBMS_CLOUD.CREATE_CREDENTIAL — see here) or its a resource principal string (see here);

declare
  status varchar2(300);
  topic_ocid VARCHAR2(100) := 'enter_topic_ocid';
  region VARCHAR2(30) := 'enter_region_here';
  cred VARCHAR2(30) := 'enter_credential_here';
begin
status := adw_execute_and_notify(topic_ocid, region, cred, 'TaskComplete', '{"task":"1","status":"SUCCESS"}');
end;

Upon completion the notification message is published to OCI Notification service and an email arrives in my inbox. Easy!

You can find out more information on the PLSQL SDK for OCI below;

PL/SQL SDK for OCI Reference

The example is very simple yet powerful to do some simple events, this example used an email subscription but could be any of OCI’s subscription types.