Many Autonomous Database customers today take advantage of Oracle’s state-of-the-art scheduler (aka DBMS_SCHEDULER) to simplify scheduling vast number of jobs in their databases. The feature-rich DBMS_SCHEDULER supports different job types such as PL/SQL blocks and stored procedures, and lets you create jobs with specific start/end times or repeat intervals. As you are reading these lines, you might be saying “Yes, we all know about DBMS_SCHEDULER. What’s new about it?”. Well, there is something new about it in Autonomous Database!

In this blog post, we are going to talk about how you can create your own PL/SQL procedures that get executed or triggered when your scheduler jobs start or complete. DBMS_SCHEDULER already supports sending email notifications for such events; however, the ability to call your own PL/SQL procedure to insert into a table or send a notification to your Slack or Microsoft Teams channels when such events occur is what we are announcing today. We are going to refer to this new option as user defined notification handler throughout this post.

To better demonstrate this new capability, we are going to be focusing on the use case in which we have a scheduler job and want to send Slack notifications to our channel whenever the job starts or completes. Let’s start!

Here are the steps that we are going to follow:

  • Create a user defined notification handler
  • Set the global scheduler attribute JOB_NOTIFICATION_HANDLER
  • Create a scheduler job
  • Enable notifications for the scheduler job
  • Run the job
  • Verify the receival of the Slack notification

Create a user defined notification handler

This is where we create our own PL/SQL procedure that will be executed when a scheduler job starts, completes etc. As mentioned earlier, we will be creating a procedure that sends a notification to a Slack channel. We will receive this notification in our Slack channel as a JSON output (We are not going to be covering the details of sending messages or query results to Slack channels here but if you are interested in learning more about it, please check out my other blog post on it).

create or replace procedure ADMIN.SEND_SLACK_NOTIFICATION(data_in clob) as
begin
  DBMS_CLOUD_NOTIFICATION.SEND_MESSAGE(
    provider          => 'slack',
    credential_name   => 'SLACK_CRED',
    message           => data_in,
    params            => JSON_OBJECT('channel' value 'C04********Y')
    );
end;
/

Set the global scheduler attribute JOB_NOTIFICATION_HANDLER

Scheduler now has a new global attribute called JOB_NOTIFICATION_HANDLER to the user defined notification handler. Please note that this attribute is mutually exclusive with the EMAIL_SERVER attribute (i.e. you cannot have both attributes set at the same time). For this demonstration, we will set the JOB_NOTIFICATION_HANDLER attribute to the notification handler we created in the previous step:

begin
  DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('job_notification_handler',
                                         'ADMIN.SEND_SLACK_NOTIFICATION');
end;
/

Create a scheduler job

The next step is to create a scheduler job. The example below creates a dummy job that is scheduled to run at a future date:

begin
  DBMS_SCHEDULER.create_job(
    job_name => 'admin.demo_job',
    job_type => 'PLSQL_BLOCK',
    job_action => 'begin null; end;',
    start_date =>  '20-APR-2023 03:00:00 AM',
    enabled  => TRUE,
    auto_drop => FALSE);
end;
/

Enable notifications for the scheduler job

You might already be familiar with the DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION that enables email notifications for a given scheduler job. The same procedure is used to trigger user defined notification handlers as well. Since it was originally designed for email notifications, some mandatory attributes (such as recipients) are still required even though they won’t be used. The following example enables our SEND_SLACK_NOTIFICATION procedure as a notification handler for the scheduler job we created in the previous step (note the events that are set to trigger these notifications):

begin
  DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION(
      job_name => 'DEMO_JOB',
      recipients => 'JOB NOTIFOCATION DEMO',
      subject => 'Job Notification-%job_owner%.%job_name%-%event_type%',
      body => '%event_type% occurred at %event_timestamp%. %error_message%',
      events => 'job_started, job_succeeded, job_completed');
end;
/

Run the job

The job we created is scheduled to run at a certain date and time. However, to speed things up a little bit, we are going to manually run it:

begin
  DBMS_SCHEDULER.RUN_JOB(
    job_name => 'admin.demo_job');
end;

Verify the receival of the Slack notification

As our last step, let’s verify that we indeed received notifications in our Slack channel (ctuzla_channel) when our job started and completed:

Slack notification for scheduler jobs

To sumamrize, DBMS_SCHEDULER helps simplify scheduling a large number of jobs in your databases. In addition to being able to send email notifications when your scheduler jobs begin or end, DBMS_SCHEDULER now can also execute user defined PL/SQL procedures for such events. In other words, you can now take actions, run queries, insert into tables, send Slack or Microsoft Teams notifications upon the start or completion of your scheduler jobs. If you’d like to learn more about this cool new feature, check out our documentation.