We've written a sample code, that should be executable in any Oracle database, to demonstrate the issue we're encountering. In a nutshell, we:
Create a job that runs a long time
We set the MAX_RUN_DURATION property of that job
We create a subscriber to listen for the event that is thrown when MAX_RUN_DURATION is exceeded.
We create another, event based job, that should kill the long running job whenever that event has been received.
Easy-Peasy only...it just doesn't work. Does anyone spot something wrong in the folowing, simple, sample block?
DECLARE
OUTLAW_JOB_NAME CONSTANT USER_SCHEDULER_JOBS.JOB_NAME%TYPE := 'JOB_THAT_NEEDS_A_KILLIN';
LAW_AND_ORDER_JOB_NAME CONSTANT USER_SCHEDULER_JOBS.JOB_NAME%TYPE := 'BAD_JOB_SHERIFF';
LAW_AND_ORDER_SUBSCRIBER_NAME CONSTANT USER_QUEUE_SUBSCRIBERS.CONSUMER_NAME%TYPE := 'TIRELESS_EYE_OF_JUSTICE';
lExcpt_JobNotFound EXCEPTION;
ls_EventNoticeRule USER_QUEUE_SUBSCRIBERS.RULE%TYPE;
PRAGMA EXCEPTION_INIT( lExcpt_JobNotFound, -27475 );
BEGIN
-- Drop the "Outlaw" Job if it already Exists
BEGIN
DBMS_SCHEDULER.DROP_JOB( job_name => OUTLAW_JOB_NAME );
EXCEPTION
WHEN lExcpt_JobNotFound THEN -- Ignore if the job doesn't exist
NULL;
END;
DBMS_SCHEDULER.CREATE_JOB( job_name => OUTLAW_JOB_NAME,
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_SESSION.SLEEP( seconds => 3600 ); /* 1 hour */ END;',
number_of_arguments => 0,
start_date => CURRENT_TIMESTAMP(),
repeat_interval => NULL,
end_date => NULL,
job_class => 'DEFAULT_JOB_CLASS',
enabled => FALSE,
auto_drop => FALSE,
comments => 'This job needs a killin'' !!!' );
-- Set the maximum time that "Outlaw" Job *should* run
DBMS_SCHEDULER.SET_ATTRIBUTE( name => OUTLAW_JOB_NAME,
attribute => 'MAX_RUN_DURATION',
value => '+000 00:05:00' ); /* 5 Minutes */
DBMS_SCHEDULER.ENABLE( name => OUTLAW_JOB_NAME );
-- Drop the "Law and Order" Job if it already Exists
BEGIN
DBMS_SCHEDULER.DROP_JOB( job_name => LAW_AND_ORDER_JOB_NAME );
EXCEPTION
WHEN lExcpt_JobNotFound THEN -- Ignore if the job doesn't exist
NULL;
END;
--Drop the "Law and Order" queue subscriber, aka "Listener" if it exists
DBMS_SCHEDULER.REMOVE_EVENT_QUEUE_SUBSCRIBER( subscriber_name => LAW_AND_ORDER_SUBSCRIBER_NAME );
/* Start listening for Scheduler Job Events */
DBMS_SCHEDULER.ADD_EVENT_QUEUE_SUBSCRIBER( LAW_AND_ORDER_SUBSCRIBER_NAME );
ls_EventNoticeRule := 'tab.user_data.object_name = ' || Chr(39) ||
OUTLAW_JOB_NAME || Chr(39) ||
' and tab.user_data.event_type = ' || Chr(39) ||
'JOB_OVER_MAX_DUR' || Chr(39);
DBMS_OUTPUT.PUT_LINE( 'ls_EventNoticeRule: ' || ls_EventNoticeRule;
/* Create a job that is triggered by reaching the timeout threshold
(5 minutes) on the "Outlaw" Job */
DBMS_SCHEDULER.CREATE_JOB( job_name => LAW_AND_ORDER_JOB_NAME,
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_SCHEDULER.STOP_JOB( ' || Chr(39) || OUTLAW_JOB_NAME || Chr(39) || ', TRUE ); END;',
event_condition => ls_EventNoticeRule,
queue_spec => 'sys.scheduler$_event_queue,' || LAW_AND_ORDER_SUBSCRIBER_NAME,
enabled => TRUE );
END;
/