2 Replies Latest reply: Nov 11, 2010 12:47 AM by 783453 RSS

    Dequeue using AQ_AGENT

    783453
      Hi,
      I am very new to using Advanced Queues. My requirement is such that when the data is enqueued to the queue, it should be automatically read and populated to tables.
      I have created queue and have come across the concept of subscribers. Can i use subscriber, if yes, how can it be used? Will I have to customize it by additional code to populate my staging tables?
      Can someone guide me how can i fully utilize the various options provided to meet my requirement?

      Thanks in Advance,
      Ritika
        • 1. Re: Dequeue using AQ_AGENT
          user11389379
          Good day! You have a long way to go. You first need to learn the concepts. You need to distinguish between P2P (peer-to-peer) and Publish/Subscribe queues (involving multicasting). The latter may require a special infrastructure and can also work better with heterogeneous systems (including Tibco Rendez-Vous and IBM Websphere MQ and the former MQ Series). I would recommend for you to download my paper and presentation from IOUG top ten at ioug.org. Regards, Anthony Noriega, OCP
          • 2. Re: Dequeue using AQ_AGENT- DBMS_AQ.REGISTER
            783453
            Hi,
            Thanks for your reply. Can you tell me specifically how can I notify in PLSQL that a message has been enqueued?
            I have read that we can use DBMS_AQ.REGISTER to notify. I have created the required procedures but it does not automatically notify when message arrives in queue.(nothing happens to the enqueued message)
            I have followed the steps as:

            1) Created Object Type, Created queue table of this object type, and queue of this table type.
            2) Written a procedure to enqueue the data to the queue:
            DECLARE
            r_enqueue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
            r_message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
            v_message_handle RAW(16);
            o_payload aq_test_subs_type;
            BEGIN
            o_payload := aq_test_subs_type('<EMPLOYEES>
            <EMP>
            <EMPNO>7369</EMPNO>
            <ENAME></ENAME>
            <JOB>CLERK</JOB>
            <HIREDATE>17-DEC-80</HIREDATE>
            <SAL>800</SAL>
            </EMP>
            <EMP>
            <EMPNO>7499</EMPNO>
            <ENAME>ALLEN</ENAME>
            <JOB>SALESMAN</JOB>
            <HIREDATE>20-FEB-81</HIREDATE>
            <SAL>1600</SAL>
            <COMM>300</COMM>
            </EMP>
            <MGR>
            <MGRNO>7369</MGRNO>
            <MGRNAME>SMITH</MGRNAME>
            <MGRJOB>CLERK</MGRJOB>
            <MGRHIREDATE>17-DEC-80</MGRHIREDATE>
            <MGRSAL>800</MGRSAL>
            </MGR>
            </EMPLOYEES>');
            DBMS_AQ.ENQUEUE(
            queue_name => 'aq_test_subs_queue',
            enqueue_options => r_enqueue_options,
            message_properties => r_message_properties,
            payload => o_payload,
            msgid => v_message_handle
            );
            COMMIT;
            END;
            3) Created pl sql callback.
            create or replace procedure dequeue_using_agent
            2 ( context in raw
            3 , reginfo in sys.aq$_reg_info
            4 , descr in sys.aq$_descriptor
            5 , payload in aq_test_subs_type
            6 , payloadl in number
            7 )
            8 is
            9 begin
            10 /*logic to dequeue data*/
            11 end dequeue_using_agent;

            4) Registered the call back:
            declare
            l_reg_info_list sys.aq$_reg_info_list;
            begin
            l_reg_info_list := sys.aq$_reg_info_list(sys.aq$_reg_info('XXCUS.aq_test_subs_queue'
            , DBMS_AQ.NAMESPACE_AQ
            , 'plsql://XXCUS.dequeue_using_agent?PR=0'
            , hextoraw('FF')));
            dbms_aq.register(l_reg_info_list, 1);
            end;

            Can you hep me by telling if I have missed any steps?

            Thanks,
            Ritika

            Edited by: Ritika on Nov 10, 2010 10:46 PM