Event-Based Jobs

Hi All,

Creating the event ques.

CREATE OR REPLACE TYPE t_event_queue_payload AS OBJECT (

 event_name VARCHAR2(30)

);

/

BEGIN

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

 DBMS_AQADM.create_queue_table(

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

END;

/

BEGIN

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

              COMMIT;

             END;',

   start_date   => SYSTIMESTAMP,

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

   queue_spec   => 'event_queue',

   enabled     => TRUE);

END;

/

DECLARE

 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;

BEGIN

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

 COMMIT;

END;

/

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?

 Thanks