This content has been marked as final. Show 5 replies
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:
Then you create MY_JOB_CONSUMER:
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; /
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.
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; /
Create a one time job, MY_JOB, set its raise_events attribute to job_succeeded and enable it:
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.
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; /
Hope that simple demonstration helps.
Nice.. this is simple..
Let me try this out and will update back the results.
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..
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.
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.?