Oracle Cloud Infrastructure Documentation

User Defined Notification Handler for Scheduler Jobs

Database Scheduler provides an email notification mechanism to track the status of periodically running or automated jobs. In addition to this, the Database Scheduler also supports user-defined PL/SQL Scheduler job notification handler procedure.

Adding a scheduler job notification handler procedure allows you to monitor scheduled or automated jobs running in your Autonomous Database.

Parent topic: Develop

About User Defined Notification Handler for Scheduler Jobs

The Database Scheduler supports job notification handler procedure that can make use of custom code to call HTTP or REST endpoints for improved monitoring of scheduler jobs in an Autonomous Database instance.

The handler procedure receives all pertinent information regarding the job, such as the job owner's name, class name, event type, and timestamp in JSON format. Based on the information, the handler procedure then takes the required action.

See DBMS_SCHEDULER for more information on Oracle Scheduler.

Configuring user defined notification handler for scheduler jobs consists of these steps:

Create a Job Notification Handler Procedure

Provides steps to create a job notification handler.

  1. Create a credential object.

    See CREATE_CREDENTIAL Procedure for more information.

    See Specifying Scheduler Job Credentials for more information.

  2. Create a Job notification handler:

    Example to create a job notification handler procedure to send a message to a Slack channel:

    CREATE OR REPLACE PROCEDURE ADMIN.SEND_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 'adw-job-nfy');
end;
/

    This example creates the SEND_NOTIFICATION procedure.

    This user defined procedure sends the provided input data as a message to the specified Slack channel.

    See Send Slack Notifications from Autonomous Database for more information.

    See SEND_MESSAGE Procedure for more information.

    Example to create a job notification handler procedure to insert the message into a table:

    CREATE TABLE ADMIN.JOB_STATUS(jnfy_data CLOB);

CREATE OR REPLACE PROCEDURE ADMIN.INSERT_JOB_STATUS(data_in CLOB) AS
  l_sessuser VARCHAR2(128) := SYS_CONTEXT('userenv','session_user');
BEGIN
  INSERT INTO ADMIN.JOB_STATUS (jnfy_data) VALUES (data_in || TO_CLOB(' : Sent By Session User : ' || l_sessuser));
  COMMIT;
END;
/

    This example creates the JOB_STATUS table and INSERT_JOB_STATUS procedure to insert the session-specific values into the table.

    You must be logged in as the ADMIN user or have CREATE ANY PROCEDURE system privilege to create a job notification handler procedure.

    Note

    An ORA-27405 is returned when you specify an invalid owner or object name as the job notification handler procedure.

    Existing DBMS_SCHEDULER procedures ADD_JOB_EMAIL_NOTIFICATION and REMOVE_JOB_EMAIL_NOTIFICATION are enhanced to support the job notification handler procedure.

    See ADD_JOB_EMAIL_NOTIFICATION Procedure and REMOVE_JOB_EMAIL_NOTIFICATION Procedure for more information.

    Use DBA_SCHEDULER_NOTIFICATIONS dictionary view to query the list of notifications for a scheduler job. See DBA_SCHEDULER_NOTIFICATIONS for more information.

Register the Job Handler Notification Procedure

Use DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE procedure to set JOB_NOTIFICATION_HANDLER attribute value to register the job handler notification procedure.

The JOB_NOTIFICATION_HANDLER attribute specifies the job handler notification procedure that you want to use.

To register the job handler notification procedure you must:

  • Be logged in as the ADMIN user or have MANAGE SCHEDULER privilege.

  • Have EXECUTE privilege on the handler procedure or EXECUTE ANY PROCEDURE system privilege.

The JOB_NOTIFICATION_HANDLER attribute and EMAIL_SERVER attribute are mutually exclusive. The ATTRIBUTE parameter of the SET_SCHEDULER_ATTRIBUTE procedure can have either the JOB_NOTIFICATION_HANDLER or the EMAIL_SERVER value at a time. You are allowed to either configure email notifications or create your notification handler for your scheduler jobs.

An ORA-27488 error is raised when you attempt to set both EMAIL_SERVER and JOB_NOTIFICATION_HANDLER global attributes.

Execute DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE procedure to register the job handler notification procedure: 

BEGIN
  DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('job_notification_handler','ADMIN.SEND_NOTIFICATION');
END;
/

This example registers the ADMIN.SEND_NOTIFICATION procedure as the job handler notification procedure for your database.

See SET_SCHEDULER_ATTRIBUTE Procedure for more information.

Execute this command to verify the job notification handler:


SELECT value FROM dba_scheduler_global_attribute WHERE attribute_name='JOB_NOTIFICATION_HANDLER';

VALUE
---------------
"ADMIN"."SEND_NOTIFICATION"

See DBA_SCHEDULER_GLOBAL_ATTRIBUTE for more information.

You must assign EXECUTE privilege to allow other users to use the job notification handler. For example: 

GRANT EXECUTE ON ADMIN.SEND_NOTIFICATION To DWUSER;

ORA-27476 ("\"%s\".\"%s\" does not exist") or ORA-27486 ("insufficient privileges") error is thrown if you do not have privilege on the job handler notification procedure.

Trigger the Job Handler Notification Procedure

You must call the DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION procedure to trigger the user defined job notification handler procedure.

The overloaded form of the DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION enables you to trigger the job notification handler procedure and send a notification. However, these notifications are not sent out in the form of an email when you have registered the job notification handler procedure. Hence, the parameters SUBJECT, and BODY are optional. The RECIPIENT parameter is still mandatory. Since this overloaded form of procedure is not sending email notifications so, you can provide any string value for the RECIPIENT parameter.
For example, the following steps create a scheduler job, add notification for the job, enable the job, verify the notification entries, show the data received by the job notification handler procedure, and remove notification for the job.
  1. Create a scheduler job in the DWUSER schema:
    BEGIN
 DBMS_SCHEDULER.CREATE_JOB(
   job_name   => 'DWUSER.MY_JOB',
   job_type   => 'PLSQL_BLOCK',
   job_action => 'BEGIN null; END;',
   enabled    => FALSE,
   auto_drop  => FALSE);
END;
/

    This creates a job DWUSER.MY_JOB with the specified attributes.

    See CREATE_JOB Procedure for more information.

  2. Configure the job to send notifications at specified events.
    BEGIN
  DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION(
    job_name   => 'DWUSER.MY_JOB',
    recipients => 'PLACEHOLDER_STRING',
    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;
/

    This procedure adds notifications for the DWUSER.MY_JOB job. The notifications are sent whenever any of the specified job state events is raised.

    See ADD_JOB_EMAIL_NOTIFICATION Procedure for more information.

  3. Enable the scheduler job.
    EXEC DBMS_SCHEDULER.ENABLE('DWUSER.MY_JOB');

    See ENABLE Procedure for more information.

    When you enable the DWUSER.MY_JOB job, the USER_SCHEDULER_NOTIFICATIONS view is populated with the job notification entries. To verify you can query the USER_SCHEDULER_NOTIFICATIONS view. For example:
    SELECT  job_name, recipient, event, subject, body
FROM user_scheduler_notifications
ORDER BY notification_owner, owner, job_name;

JOB_NAME    RECIPIENT            EVENT          SUBJECT         BODY
-------    ------------------   -------------  --------------- ---------------
MY_JOB     placeholder_string   JOB_STARTED    Job Notificatio %event_type% oc
                                               n-%job_owner%.% curred at %even
                                               job_name%-%even t_timestamp%. %
                                               t_type%         error_message%

MY_JOB     placeholder_string  JOB_SUCCEEDED    Job Notificatio %event_type% oc
                                                n-%job_owner%.% curred at %even
                                                job_name%-%even t_timestamp%. %
                                                t_type%         error_message%
 	
MY_JOB    placeholder_string  JOB_COMPLETED    Job Notificatio %event_type% oc
                                               n-%job_owner%.% curred at %even
                                               job_name%-%even t_timestamp%. %
                                               t_type%         error_message%

    See USER_SCHEDULER_NOTIFICATIONS View for more information.

    When the job DWUSER.MY_JOB is executed and any of the specified job state events is raised, the job notification handler procedure is triggered and receives the specified information as input. For example, the ADMIN.SEND_NOTIFICATION job notification handler procedure receives the following: 

    {"job_owner":"DWUSER","job_name":"MY_JOB","job_class_name":"DEFAULT_JOB_CLA
SS","event_type":"JOB_STARTED","event_timestamp":"12-JAN-23 08.13.46.193306
 PM UTC","error_code":0,"error_msg":null,"sender":null,"recipient":"data_lo
ad_pipeline","subject":"Job Notification-DWUSER.MY_JOB-JOB_STARTED","msg_te
xt":"JOB_STARTED occurred at 12-JAN-23 08.13.46.193306 PM UTC. ","comments"
:"User defined job notification handler"}
 
{"job_owner":"DWUSER","job_name":"MY_JOB","job_class_name":"DEFAULT_JOB_CLA
SS","event_type":"JOB_SUCCEEDED","event_timestamp":"12-JAN-23 08.13.46.2863
44 PM UTC","error_code":0,"error_msg":null,"sender":null,"recipient":"data_
load_pipeline","subject":"Job Notification-DWUSER.MY_JOB-JOB_SUCCEEDED","ms
g_text":"JOB_SUCCEEDED occurred at 12-JAN-23 08.13.46.286344 PM UTC. ","com
ments":"User defined job notification handler"}
  4. Remove the job notification. For example:
    EXEC DBMS_SCHEDULER.REMOVE_JOB_EMAIL_NOTIFICATION('DWUSER.MY_JOB');

    See REMOVE_JOB_EMAIL_NOTIFICATION Procedure for more information.

De-Register the Job Handler Notification Procedure

Use DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE to de-register the job handler notification procedure.

To de-register the job handler notification procedure you must be logged in as the ADMIN user or have MANAGE SCHEDULER privilege.

Example to de-register the job handler notification procedure:

BEGIN
  DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE ('job_notification_handler','');
END;
/