Loading

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;
/

4 Responses to “Steps for Configuring Advanced Queues in Oracle 10g”

John said...
February 9, 2011 at 9:17 PM

Nice Compilation!!!


Anonymous said...
April 18, 2012 at 4:53 AM

Admirable illustration designed for novice


Blogger said...
January 29, 2013 at 4:56 AM

I visited this blog it is nice and very informative for apps developers.
Oracle Iprocurement


Matsoleng John Tleane said...
November 23, 2016 at 4:13 AM

so perfect.. keep up the good work...


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.