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.
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.
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.
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:
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.
Use DBA_SCHEDULER_NOTIFICATIONS dictionary view to query the list of notifications for a scheduler job. See DBA_SCHEDULER_NOTIFICATIONS for more information.
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.
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.
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.
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%
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"}