Here is an example of how you would use event-based scheduling to create an Oracle Scheduler job that has to run after the successful completion of another job.
First you let the database user that will own the jobs subscribe to the Scheduler event queue using the ADD_EVENT_QUEUE_SUBSCRIBER procedure. “my_agent”, or whatever name you use, is the name of the Oracle Streams Advanced Queuing (AQ) agent to be used to subscribe to the Scheduler event queue. This call both creates a subscription to the Scheduler event queue and grants the user permission to dequeue using the designated agent. The subscription is rule-based. The rule permits the user to see only events raised by jobs that the user owns, and filters out all other messages:
BEGIN sys.DBMS_SCHEDULER.add_event_queue_subscriber ('my_agent'); END; /
Create an event-based job, name it MY_JOB_CONSUMER for example, that will run after the successful completion of another job named MY_JOB:
BEGIN sys.DBMS_SCHEDULER.create_job ( job_name => 'MY_JOB_CONSUMER', job_type => 'PLSQL_BLOCK', job_action => '/* Add you code here */ NULL;', event_condition => 'tab.user_data.event_type = ''JOB_SUCCEEDED'' and tab.user_data.object_name = ''MY_JOB''', queue_spec => 'sys.scheduler$_event_queue,my_agent', enabled => TRUE); END; /
Now, MY_JOB_CONSUMER is waiting for a message to arrive in sys.scheduler$_event_queue. It will dequeue it only if the condition in event_condition is true.
Create a one time job, MY_JOB, and set its raise_events attribute to job_succeeded then enable it:
BEGIN sys.DBMS_SCHEDULER.create_job (job_name => 'MY_JOB', job_type => 'PLSQL_BLOCK', job_action => '/* Add you code here */ NULL;', start_date => SYSTIMESTAMP AT TIME ZONE 'US/Pacific', auto_drop => TRUE, enabled => FALSE); DBMS_SCHEDULER.set_attribute ('MY_JOB', 'raise_events', DBMS_SCHEDULER.job_succeeded); sys.DBMS_SCHEDULER.enable ('MY_JOB'); END; /
When MY_JOB executes, it will enqueue a message in sys.scheduler$_event_queue. This will trigger MY_JOB_CONSUMER to start executing.
PS: This post was tested on Oracle Database 188.8.131.52 and triggered by this OTN thread.