User Defined Notification Handler for Scheduler Jobs
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. - Create a Job Notification Handler Procedure
Provides steps to create a job notification handler. - Register the Job Handler Notification Procedure
UseDBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE
procedure to setJOB_NOTIFICATION_HANDLER
attribute value to register the job handler notification procedure. - Trigger the Job Handler Notification Procedure
You must call theDBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION
procedure to trigger the user defined job notification handler procedure. - De-Register the Job Handler Notification Procedure
UseDBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE
to de-register the job handler notification procedure.
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 as described in: Create a Job Notification Handler Procedure.
-
Register the job notification handler procedure for the database as described in: Register the Job Handler Notification Procedure.
-
Trigger the job notification handler procedure as described in: Trigger the Job Handler Notification Procedure
-
De-Register the job notification handler procedure for the database as described in: De-Register the Job Handler Notification Procedure.
Parent topic: User Defined Notification Handler for Scheduler Jobs
Create a Job Notification Handler Procedure
Provides steps to create a job notification handler.
Parent topic: User Defined Notification Handler for Scheduler Jobs
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.
-
Be logged in as the ADMIN user or have
MANAGE SCHEDULER
privilege. -
Have
EXECUTE
privilege on the handler procedure orEXECUTE 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.
Parent topic: User Defined Notification Handler for Scheduler Jobs
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.
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.
Parent topic: User Defined Notification Handler for Scheduler Jobs
De-Register the Job Handler Notification Procedure
Use DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE
to de-register the job handler notification procedure.
MANAGE SCHEDULER
privilege.
Example to de-register the job handler notification procedure:
BEGIN
DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE ('job_notification_handler','');
END;
/
Parent topic: User Defined Notification Handler for Scheduler Jobs