Hi,
I am struggling several days with following issue.
I am trying to implement event based job.
At start all was working fine.
But after several payload type modifications + several times recreated queue + recreated scheduled job ... scheduler isn't triggered by event queue anymore.
I see that records are stored in event queue.But scheduled job doesn't process them.
I tried to run this script in the same DB's new schema. I added all necessary grants for AQ + type + job creation
In fresh schema this script is working fine and queue is being processed by job.
Could i have broken some data dictionaries or something else?
I don't have any ideas what should i check now.
CREATE TABLE scheduler_test (
id NUMBER(10) NOT NULL,
created_date DATE NOT NULL,
CONSTRAINT scheduler_test_pk PRIMARY KEY (id)
);
/
CREATE SEQUENCE scheduler_test_seq;
/
select * from user_types;
select * from user_type_attrs;
/
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',
MESSAGE_GROUPING => DBMS_AQADM.TRANSACTIONAL,
sort_list => 'COMMIT_TIME',
compatible => '10.1',
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 => false);
dbms_scheduler.set_attribute('event_based_job','parallel_instances',TRUE);
dbms_scheduler.enable('event_based_job');
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;
/
SELECT * FROM scheduler_test;
cleanup script
-- Remove the job.
begin
DBMS_SCHEDULER.drop_job('event_based_job');
-- Stop the event queue.
DBMS_AQADM.stop_queue (queue_name => 'event_queue');
-- Drop the event queue.
DBMS_AQADM.drop_queue (queue_name => 'event_queue');
-- Remove the queue table.
DBMS_AQADM.drop_queue_table(queue_table => 'event_queue_tab');
end;
/
DROP TYPE t_event_queue_payload;
DROP TABLE scheduler_test;
DROP SEQUENCE scheduler_test_seq;
PURGE RECYCLEBIN;