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