Wednesday, June 16, 2010
Steps for Configuring Advanced Queues in Oracle 10g
In this post I tried to explain the basic steps and scripts required to test the Advanced Queues in Oracle 10g
Preliminary Steps
-- Granting the necessary Roles to the concerned User for handling Queues
GRANT CREATE SESSION TO scott;
GRANT CREATE PROCEDURE TO scott;
GRANT CREATE SESSION TO scott;
GRANT CREATE TABLE TO scott;
GRANT CREATE TYPE TO scott;
GRANT aq_administrator_role TO scott;
GRANT EXECUTE ON dbms_aq TO scott;
GRANT EXECUTE ON dbms_aqadm TO scott;
-- Connect as Scott/Tiger and then execute the following steps
-- 1. Creation of Necessary type which is to be used as message paylod
CREATE TYPE demo_queue_payload_type AS OBJECT
( message VARCHAR2(4000) );
/
-- 2. Creation of Queue Table
BEGIN
DBMS_AQADM.CREATE_QUEUE_TABLE (
queue_table => 'demo_queue_table',
queue_payload_type => 'demo_queue_payload_type',
multiple_consumers => TRUE
);
END;
/
-- 3. Creation and starting Of Queue
BEGIN
DBMS_AQADM.CREATE_QUEUE (
queue_name => 'demo_queue',
queue_table => 'demo_queue_table'
);
DBMS_AQADM.START_QUEUE (
queue_name => 'demo_queue'
);
END;
/
-- 4. Creation of Subscriber
BEGIN
DBMS_AQADM.ADD_SUBSCRIBER (
queue_name => 'demo_queue',
subscriber => SYS.AQ$_AGENT(
'demo_queue_subscriber',
NULL,
NULL )
);
END;
/
-- 5. Check the Subscriber
SELECT *
FROM ALL_QUEUE_SUBSCRIBERS
WHERE queue_name = 'DEMO_QUEUE';
-- 6. Enqueue The Queue with the message properties sothat it can dequeued uniquely
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(
'TEST7'
);
r_message_properties.correlation := 'TRY3';
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(
'*** message id is [' || v_message_handle || '] ***'
);
COMMIT;
END;
/
-- 7. Check the enqueued message
SELECT *
FROM demo_queue_table;
SELECT user_data
FROM aq$demo_queue_table;
-- 8. Dequeue the enqueued message whoose correlation id is TRY3
-- Remember that Dequeue can happen without specifying the correlation_id or
-- Msg_id. In that case DEQUEUE follows the FIFO algorithm.
SET serveroutput ON
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.navigation := DBMS_AQ.FIRST_MESSAGE;
r_dequeue_options.dequeue_mode := DBMS_AQ.REMOVE;
r_dequeue_options.consumer_name:= 'DEMO_QUEUE_SUBSCRIBER';
r_dequeue_options.visibility := DBMS_AQ.IMMEDIATE;
--r_dequeue_options.msgid := '83EBC373A50B4BADADFE6C32947A2165';
--r_dequeue_options.correlation := 'MADDY3';
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 || '] ***'
);
END;
/
-- 10. Finally dropping the queues and queue table
BEGIN
dbms_aqadm.stop_queue( queue_name => 'DEMO_QUEUE'
,wait => TRUE
);
dbms_aqadm.drop_queue(queue_name => 'DEMO_QUEUE');
-- Drop rx Queue Table
dbms_aqadm.drop_queue_table(
queue_table => 'DEMO_QUEUE_TABLE'
, FORCE => TRUE
);
END;
/
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
3 Responses to “Steps for Configuring Advanced Queues in Oracle 10g”
February 9, 2011 at 9:17 PM
Nice Compilation!!!
April 18, 2012 at 4:53 AM
Admirable illustration designed for novice
November 23, 2016 at 4:13 AM
so perfect.. keep up the good work...
Post a Comment