News, views, tips and tricks on Oracle and other fun stuff

Oracle Scheduler Event-Based Jobs are Useful, Here is a Quick Example

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:

   sys.DBMS_SCHEDULER.add_event_queue_subscriber ('my_agent');

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:

   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);

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:

   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');

When MY_JOB executes, it will enqueue a message in sys.scheduler$_event_queue. This will trigger MY_JOB_CONSUMER to start executing.

Simple, eh?

PS: This post was tested on Oracle Database and triggered by this OTN thread.

Filed in Oracle on 12 Apr 11 | Tags: ,

Reader's Comments

  1. |

    is it different from a chain ?


  2. |

    Great question Haki. I was waiting for someone to ask this question. You can certainly use a job chain to implement the job dependency described in the example. In fact, you could have both a chain and an event-based job go hand in hand. You could even define a chain step that waits for a specific event (using DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP).

    For example, imagine you have a complex chain with 10 steps. You want to stop the whole job chain when step 2 fails for example. A clean way of implementing this requirement is to create an event handler to raise a Scheduler event. The job that consumes this event would terminate the job chain. In fact, you could build this “exception handling” for all steps. And let one event-based job handle the exceptions.

  3. |

    have you had any experience with chains in 10g and 11g ? would you say this solution is stable ?

    i must say that i am a bit afraid of using events and chains in production systems (it even took me a while to gain confidence in dbms_schduler).


    • |

      Why are you afraid?

      The Oracle Scheduler is a very stable and superior feature in the Oracle database. Of course, like any other software product, there will always be a few bugs here and there.

      I have successfully used job chains and event-based jobs in production environments.