Forum Stats

  • 3,838,876 Users
  • 2,262,407 Discussions


Event-Based Jobs

User_3UMC3 Member Posts: 34 Red Ribbon

Hi All,

Creating the event ques.

CREATE OR REPLACE TYPE t_event_queue_payload AS OBJECT (

 event_name VARCHAR2(30)




 -- Create a queue table to hold the event queue.


  queue_table    => 'event_queue_tab',

  queue_payload_type => 't_event_queue_payload',

  multiple_consumers => TRUE,

  comment      => 'Queue Table For Event Messages');

 -- Create the event queue.

 DBMS_AQADM.create_queue (

  queue_name => 'event_queue',

  queue_table => 'event_queue_tab');

 -- Start the event queue.

 DBMS_AQADM.start_queue (queue_name => 'event_queue');




  DBMS_SCHEDULER.create_job (

   job_name    => 'event_based_job',

   job_type    => 'PLSQL_BLOCK',

   job_action   => 'BEGIN

              INSERT INTO scheduler_test (id, created_date)

              VALUES (scheduler_test_seq.NEXTVAL, SYSDATE);



   start_date   => SYSTIMESTAMP,

   event_condition => 'tab.user_data.event_name = ''give_me_a_prod''',

   queue_spec   => 'event_queue',

   enabled     => TRUE);




 l_enqueue_options   DBMS_AQ.enqueue_options_t;

 l_message_properties DBMS_AQ.message_properties_t;

 l_message_handle   RAW(16);

 l_queue_msg      t_event_queue_payload;


 l_queue_msg := t_event_queue_payload('give_me_a_prod');

 DBMS_AQ.enqueue(queue_name     => 'event_queue',

         enqueue_options   => l_enqueue_options,

         message_properties => l_message_properties,

         payload       => l_queue_msg,

         msgid        => l_message_handle);




after ran the above plsql block, scheduler got triggered and insert the data into table. this is working fine.

my requirement :

1. i dropped the job ->>( job_name => 'event_based_job').after that, i ran the above plsql block 2 times.

    2 entries are there event_queue_tab table.

2. after that ,i will create the DBMS_SCHEDULER.create_job 

3.once created the schedular, after 1 min later this job need to be trigger ,why because 2 entries are the in event_queue_tab table  and it not happing.

 if there any solution for this?




  • User_H3J7U
    User_H3J7U Member Posts: 1,062 Gold Trophy

    The queue triggered event job only when the enqueue occurs. Moreover, if a new enqueue occurs during the job execution, the scheduler will skip that event.

    Simplest way is to start a single non-event job with a continuous dequeuing. Or enable multiple event job instances and dequeue all messages on startup. The third option is to register the pl/sql callback, but you still need to read messages on startup. Pl/sql callback is starting up to 20 processes on simultaneous enqueue.

  • User_3UMC3
    User_3UMC3 Member Posts: 34 Red Ribbon

    i enable this one as well, still ,it is not working.


    As per your comment " enable multiple event job instances and dequeue all messages on startup. "

    do you have sample,please share it?

  • User_H3J7U
    User_H3J7U Member Posts: 1,062 Gold Trophy

    Parallel_instances is needed to solve this problem:


    The Scheduler runs the event-based job for each occurrence of an event that matches event_condition. However, by default, events that occur while the job is already running are ignored; the event gets consumed, but does not trigger another run of the job. Beginning in Oracle Database 11g Release 1 (11.1), you can change this default behavior by setting the job attribute PARALLEL_INSTANCES to TRUE. In this case, an instance of the job is started for every instance of the event, and all job instances are lightweight jobs. See the SET_ATTRIBUTE procedure in Oracle Database PL/SQL Packages and Types Reference for details.

    How to dequeue messages from queue, you can find in the documentation Advanced Queuing User's Guide (pl/sql). See also Administrators Guide about event based jobs.

  • User_3UMC3
    User_3UMC3 Member Posts: 34 Red Ribbon

    i tried with parallel instance,it is not working.still event are the in que table.