模拟消息进入异常队列的场景
如何在入队/出队/浏览oracle aq期间模拟异常，以便消息进入异常队列。 我是新手，但还没有经过用户和消费者。 我所做的是----
BEGIN dbms_aqadm.drop_queue_table (
queue_table => 'demo_queue_table',
force => TRUE);
END;
--------------------- Creation of queue table
BEGIN
DBMS_AQADM.CREATE_QUEUE_TABLE (
queue_table => 'demo_queue_table',
queue_payload_type => 'demo_queue_payload_type'
);
END;
-------------------- Creation and starting of the queue
BEGIN
DBMS_AQADM.CREATE_QUEUE (
queue_name => 'demo_queue',
queue_table => 'demo_queue_table'
);
DBMS_AQADM.START_QUEUE (
queue_name => 'demo_queue'
);
END;
---------------------Exception Queue
BEGIN
dbms_aqadm.create_queue(
queue_name => 'demo_queue_e',
queue_table => 'demo_queue_table',
queue_type => DBMS_AQADM.EXCEPTION_QUEUE,
dependency_tracking => FALSE,
comment => 'EXCEPTION QUEUE OF DEMO QUEUE TABLE');
-- start queue
dbms_aqadm.start_queue('DEMO_QUEUE_E',enqueue => FALSE, dequeue => TRUE);
END;
BEGIN
dbms_aqadm.start_queue('AQ$_DEMO_QUEUE_TABLE_E',enqueue => FALSE, dequeue => TRUE);
END;
------------- Enqueue with Exception queue specified
DECLARE
r_enqueue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
r_message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
v_message_handle RAW(16);
o_payload demo_queue_payload_type;
BEGIN
o_payload := demo_queue_payload_type('Message');
r_message_properties.exception_queue := 'DEMO_QUEUE_E';
DBMS_AQ.ENQUEUE(
queue_name => 'demo_queue',
enqueue_options => r_enqueue_options,
message_properties => r_message_properties,
payload => o_payload,
msgid => v_message_handle
);
DBMS_OUTPUT.PUT_LINE('*** Exception Queue is [' || o_payload.message || '] ***');
DBMS_OUTPUT.PUT_LINE('*** Exception Queue is [' || r_message_properties.exception_queue || '] ***');
COMMIT;
END;
------------------------------ BRowse
DECLARE
r_dequeue_options DBMS_AQ.DEQUEUE_OPTIONS_T;
r_message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
v_message_handle RAW(16);
o_payload demo_queue_payload_type;
BEGIN
r_dequeue_options.dequeue_mode := DBMS_AQ.REMOVE;
DBMS_AQ.DEQUEUE(
queue_name => 'demo_queue',
dequeue_options => r_dequeue_options,
message_properties => r_message_properties,
payload => o_payload,
msgid => v_message_handle
);
DBMS_OUTPUT.PUT_LINE('*** Browsed message is [' || o_payload.message || '] ***');
-- DBMS_OUTPUT.PUT_LINE('*** Browsed message is [' || r_message_properties.exception_queue || '] ***');
END;
-----------------------------------Dequeue
DECLARE
r_dequeue_options DBMS_AQ.DEQUEUE_OPTIONS_T;
r_message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
v_message_handle RAW(16);
o_payload demo_queue_payload_type;
no_messages exception;
pragma exception_init (no_messages, -25263);
BEGIN
DBMS_AQ.DEQUEUE(
queue_name => 'demo_queue',
dequeue_options => r_dequeue_options,
message_properties => r_message_properties,
payload => o_payload,
msgid => v_message_handle
);
DBMS_OUTPUT.PUT_LINE('*** Dequeued message is [' || o_payload.message || '] ***');
COMMIT;
END;
---------------------------------start the error queue---------------------------------------------
exec dbms_aqadm.start_queue('AQ$_DEMO_QUEUE_TABLE_E',false, true);
我现在很熟悉入队，出队，浏览功能。 我只想看看异常队列是如何工作的，并且在异常队列（浏览和出队）上做一些实验。 在理论上，它不可能排队在例外队列中。 所以我必须生成一些场景，在入队，出队或浏览期间，消息进入异常队列。
1）Yod不需要创建异常队列。 它是隐式创建的durgin DBMS_AQADM.CREATE_QUEUE。 select * from user_queues; 。
2）设置消息到期时间。
declare
v_enqueue_options dbms_aq.enqueue_options_t;
v_message_properties dbms_aq.message_properties_t;
v_message_handle raw(16);
recipients DBMS_AQ.aq$_recipient_list_t;
begin
v_message_properties.expiration := 1; -- <- here
dbms_aq.enqueue(queue_name => 'schema.queue_name'
,enqueue_options => v_enqueue_options
,message_properties => v_message_properties
,payload => self
,msgid => v_message_handle);
end;
上一篇: Simulate a scenario where message goes into exception queue