Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Event-Based Jobs

murali rishnaJun 7 2021

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

Comments

Post Details

Added on Jun 7 2021
4 comments
805 views