Hi,
I have created a test case, I want to be able to stop a scheduled job if an event is raised based on the max duration time. In my test case I am simply inserting into a table to prove the event is being raised. I can not get the event to raise.
The idea is to create a job "MyTask" execute this Job every 2 minutes, but has a sleep command for 90 seconds. If the Job takes longer than 1 minute, report (or stop the job). In my code below I am reporting into a table. I have set the max run duration attribute, and then monitor for the event to be raised. The job "MyTask" is successfully executing, but the event is not being raised.
Can anyone help - am I missing something or failed to set up something?
Following is my code:
set heading off pagesize 0;
alter session set nls_date_format = 'DD-Mon-RRRR HH24:MI:SS';
create table t (x varchar2(500), y date);
create or replace procedure myTask is
begin
dbms_lock.sleep('90');
insert into t (x,y) values ('Ran', sysdate);
end myTask;
/
show errors;
create or replace procedure statStopper (p_msg IN sys.scheduler$_event_info) as
begin
insert into t (x,y) values ('Stopping', sysdate);
commit;
-- dbms_scheduler.stop_job(job_name => p_msg.object_owner||'.'||p_msg.object_name,force => FALSE);
end statStopper;
/
show errors;
begin
DBMS_SCHEDULER.drop_job('DD_TEST');
DBMS_SCHEDULER.drop_job('STATKILLER');
dbms_scheduler.remove_event_queue_subscriber('STATSNIPER');
DBMS_SCHEDULER.drop_program('statReadEvents');
end;
/
declare
v_start_date timestamp with time zone;
begin
select localtimestamp at time zone 'Europe/London' into v_start_date from dual;
DBMS_SCHEDULER.create_job (
job_name => 'DD_TEST',
job_type => 'STORED_PROCEDURE',
job_action => 'myTask',
start_date => v_start_date,
repeat_interval => 'freq=minutely; interval=02; bysecond=0;',
enabled => TRUE);
dbms_scheduler.set_attribute(name=>'DD_TEST',attribute=>'MAX_RUN_DURATION',value=>numToDSInterval(1, 'minute'));
dbms_scheduler.create_program(
program_name => 'STATREADEVENTS',
program_type => 'STORED_PROCEDURE',
program_action=>'statStopper',
number_of_arguments => 1);
dbms_scheduler.define_metadata_argument(
program_name => 'STATREADEVENTS',
metadata_attribute => 'EVENT_MESSAGE',
argument_position => 1);
dbms_scheduler.enable(name => 'STATREADEVENTS');
dbms_scheduler.add_event_queue_subscriber('statsniper');
dbms_aqadm.enable_db_access('statsniper','COREHR');
dbms_scheduler.create_job (job_name=>'STATKILLER',
program_name => 'STATREADEVENTS',
event_condition => null,
queue_spec =>'sys.scheduler$_event_queue,statsniper',
enabled=>true);
COMMIT;
end;
/
select * from dba_scheduler_jobs where program_name = 'STATREADEVENTS';
SELECT * FROM dba_scheduler_jobs where job_name in ('DD_TEST','STATKILLER');
SELECT SELECT log_date||' '||status||' '||run_duration from dba_scheduler_job_run_details where job_name='DD_TEST' and trunc(log_date) = trunc(sysdate) order by log_id desc;
select