This discussion is archived
5 Replies Latest reply: May 31, 2013 12:15 PM by 1012164 Branched to a new discussion. RSS

Events Based Scheduling

818464 Newbie
Currently Being Moderated
Database Ver. 11gR2 (11.2.0.2.0)

Hi Guys,
How do you run jobs in oracle scheduler based on events? I have a job that needs to run after the successful completion of another job. I don't want to use chains. I have searched on the web, but all the examples I see are where a temp table is created and a row is inserted and then another PL/SQL block is run to manually pass a 'GO' value. I am looking for the a job to raise the JOB_SUCCEEDED event and then another job kicks in based on this event.
Any detailed examples would be greatly appreciated. Also if using any other PL/SQL package, what necessary privileges would be required to execute that package.

Thanks in advance.

VB
  • 1. Re: Events Based Scheduling
    EddieAwad Oracle ACE Director
    Currently Being Moderated
    Here is a simple example. You create a job, MY_JOB, that raises the job_succeeded event. You also create an event-based job, MY_JOB_CONSUMER, that consumes event messages from sys.scheduler$_event_queue but only for job_succeeded events raised by MY_JOB.

    First you let the user 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:
    DECLARE
       agent_already_subscribed_exp   EXCEPTION;
       PRAGMA EXCEPTION_INIT (agent_already_subscribed_exp, -24034);
    BEGIN
       sys.DBMS_SCHEDULER.add_event_queue_subscriber ('my_agent');
    EXCEPTION
       WHEN agent_already_subscribed_exp
       THEN
          NULL;
    END;
    /
    Then you create MY_JOB_CONSUMER:
    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, set its raise_events attribute to job_succeeded and 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. MY_JOB_CONSUMER will then pick it up and consume it.

    Hope that simple demonstration helps.
  • 2. Re: Events Based Scheduling
    818464 Newbie
    Currently Being Moderated
    Nice.. this is simple..
    Thanks Eddie
    Let me try this out and will update back the results.

    Thanks again
    VB
  • 3. Re: Events Based Scheduling
    818464 Newbie
    Currently Being Moderated
    Oh my.. this really was simple..
    I ran the test and it worked just fine.. as we had expected it to run..
    Extremely helpful... can't thank enough..

    Thanks again Eddie..

    VB
  • 4. Re: Events Based Scheduling
    user3982832 Explorer
    Currently Being Moderated
    Hello – I need to trigger execution of stored procedure using event based scheduling. Here the scenario...

    1. Siebel application submits the Order

    2. The Order Id and XML Payload is inserted in Oracle JMS queue (In COORID and USER_DATA respectively)

    Now I need to trigger Oracle stored procedure and pass Order Id and XML message as parameters to the procedure

    How can I do that?


    Appreciate any help.



    Thanks
  • 5. Re: Events Based Scheduling
    1012164 Newbie
    Currently Being Moderated
    Eddie: you had same mentioned on your blog and had following comment:

    "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. "

    I am not following your design above but looks very interesting way to have a job that depends on chain and event and create event handler etc. Any example that walks through where job has both chain step and event dependency ? Also exampe that walks through of creating separate event handler as described above.?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points