Hi,
I am trying to raise job_over_max_dur event, and trigger an email notification, for a simple job that I created. However I am unable to do so. Below is the code snippet which I am using. Please suggest if I am missing anything.
P.S. If I give job_all_events while adding email notification, I am able to receive JOB_STARTED and JOB_SUCCEEDED notification.
1. Create a simple dbms job.
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
JOB_NAME => 'TEST_JOB1',
JOB_TYPE => 'PLSQL_BLOCK',
JOB_ACTION => 'DECLARE
V_MIN1 NUMBER;
V_MIN2 NUMBER;
DIFF NUMBER;
BEGIN
SELECT TO_NUMBER(TO_CHAR(SYSDATE,''MI'')) INTO V_MIN1 FROM DUAL;
WHILE 1=1
LOOP
SELECT TO_NUMBER(TO_CHAR(SYSDATE,''MI'')) INTO V_MIN2 FROM DUAL;
DIFF := V_MIN2 - V_MIN1;
EXIT WHEN DIFF >= 2;
END LOOP;
END;',
AUTO_DROP => FALSE,
ENABLED => FALSE);
END;
/
2.Creating job notification.
BEGIN
DBMS_SCHEDULER.set_scheduler_attribute('email_server', 'localhost:25');
DBMS_SCHEDULER.set_scheduler_attribute('email_sender', 'do_not_reply@xyz.com');
END;
/
BEGIN
DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
job_name => 'TEST_JOB1',
recipients => 'yyyyy@abc.com',
sender => 'do_not_reply@xyz.com',
subject => 'Scheduler Job Notification-%job_owner%.%job_name%-%event_type%',
body => '%event_type% occurred at %event_timestamp%. %error_message%',
events => 'job_over_max_dur');
END;
/
BEGIN
dbms_scheduler.set_attribute ( 'TEST_JOB1' , 'max_run_duration' , interval '60' second);
END;
/
------------------------------Moderator action -------------------------
removed private information (email addresses!
User, please be careful not to post private information!
-------------------------------------------------------------------------------