Fun with event based oracle sheduling

Goal: On  event triggered by application another job should execute

Step 1: Create job

1.1 Table job_output

create table job_output(systmstmp timestamp with time zone,msg varchar2(4000));

1.2* Event_test_proc procedure 

create or replace procedure event_test_proc(MESSAGE IN sys.scheduler$_event_info)as

begin
  insert into job_output values(systimestamp,message.object_name);
  insert into job_output values(systimestamp,message.event_type);
  commit;
end;
/

1.3 Test program for scheduler

BEGIN
  dbms_scheduler.create_program ( program_name => 'event_test_prog'
  , program_action=> 'event_test_proc'
  , program_type => 'stored_procedure'
  , number_of_arguments => 1
  , enabled => FALSE) ;
  dbms_scheduler.define_metadata_argument ( 'event_test_prog','event_message',1);
  dbms_scheduler.enable('event_test_prog');
END;
/

1.4 Add subscriber for oracle streams advance queue

begin
dbms_scheduler.add_event_queue_subscriber('my_agent');
end;
/

1.5** Event monitoring job 

BEGIN
  dbms_scheduler.create_job(job_name => 'event_test_job'
  , program_name => 'event_test_prog'
  , event_condition => 'tab.user_data.event_type = ''JOB_OVER_MAX_DUR'''
  , queue_spec =>'sys.scheduler$_event_queue,my_agent'
  , enabled=>true);
END;
/

Step 2*** : Now create a test job

BEGIN
  dbms_scheduler.create_job ( job_name => 'MAX_TIME_TEST'
  ,job_type => 'PLSQL_BLOCK'
  ,job_action => 'BEGIN         dbms_lock.sleep(90);      END;'
  ,start_date => SYSTIMESTAMP
  ,enabled => FALSE
  ,comments => 'TEST');
  dbms_scheduler.set_attribute(name => 'MAX_TIME_TEST'
  ,attribute => 'max_run_duration'
  ,value => interval '61' second);
  dbms_scheduler.set_attribute(name => 'MAX_TIME_TEST', attribute => 'raise_events',value => DBMS_SCHEDULER.job_all_events);
  dbms_scheduler.enable('MAX_TIME_TEST');
END;
/

Step 3 : Scheduler Monitoring view

select M.state,M.* from ALL_SCHEDULER_JOBS M where owner='RADHEY';

select * from ALL_SCHEDULER_JOB_LOG where owner='RADHEY' order by log_date desc;

select * from ALL_SCHEDULER_JOB_RUN_DETAILS where owner='RADHEY' order by req_start_date desc;

select * from ALL_SCHEDULER_RUNNING_JOBS where owner='RADHEY';

Step 4: Result time

select * from job_output order by 1 desc;

NOTE: 

*1. The program event_test_proc can be anything like email notification, stop running job , postpone job to run at later time etc. It is totally based on the requirement.
**2 event_type can be : "JOB_STARTED", "JOB_SUCCEEDED", "JOB_FAILED", "JOB_BROKEN", "JOB_COMPLETED", "JOB_STOPPED", "JOB_SCH_LIM_REACHED", "JOB_DISABLED", "JOB_CHAIN_STALLED", "JOB_OVER_MAX_DUR".
***3 attribute raise_events can not raise JOB_OVER_MAX_DUR event. max_run_duration attribute with non null value is required to raise this event.

Disclaimer: I am not the original author of above scripts but I have tested this on my machine and implemented in production system. The job in production stops long running job and schedules to run that job on later time. These notes are for my own reference.