0 Replies Latest reply: Dec 31, 2013 11:01 AM by Sadock RSS

    Dequeueing propagated message using PL/SQL notification

    Sadock

      Dear all

       

      I want to dequeue  messages using PL/SQL callback proceduare. The message is enqueued using propagation from another database. However it seem the callback procedure is not fired because I am not getting what i want the procedure to do when the message arrive in the queue. Using the same procedure when enqueueing a message locally execute the procedure

       

      Here is my PL/SQL callback procedure:

       

      CREATE OR REPLACE PROCEDURE strmadmin.dequeue_xmass_callback_proc(

                                  context  RAW,

                                  reginfo  SYS.AQ$_REG_INFO,

                                  descr    SYS.AQ$_DESCRIPTOR,

                                  payload  RAW,

                                  payloadl NUMBER

                                  )

      AS

         dequeue_options     DBMS_AQ.DEQUEUE_OPTIONS_T;

         message_properties  DBMS_AQ.MESSAGE_PROPERTIES_T;

         message_handle      RAW(16);

         message             strmadmin.xmass_payload;

      BEGIN

         dequeue_options.msgid := descr.msg_id;

         dequeue_options.consumer_name := descr.consumer_name;

         dequeue_options.wait := DBMS_AQ.NO_WAIT;

         DBMS_AQ.DEQUEUE(

            queue_name          => 'descr.xmass_queue',

            dequeue_options     => dequeue_options,

            message_properties  => message_properties,

            payload             => message,

            msgid               => message_handle

            );

         INSERT INTO strmadmin.xmass_message(sender_id, subject, text)

         VALUES (message.sender_id, message.subject, message.text);

         COMMIT;

      END;

      /

       

      Here is my subscriber and notifications configurations script

       

      DECLARE

         subscriber SYS.AQ$_AGENT;

         reginfo    SYS.AQ$_REG_INFO;

         reg_list   SYS.AQ$_REG_INFO_LIST;             

      BEGIN

         subscriber := SYS.AQ$_AGENT(

                           name     => 'xmass_queue_subscriber',

                           address  => NULL,

                           protocol => NULL

                           );

         DBMS_AQADM.ADD_SUBSCRIBER (

            queue_name     => 'strmadmin.xmass_queue',

            subscriber     => subscriber,

            queue_to_queue => TRUE

            );

         reginfo := SYS.AQ$_REG_INFO(

                         name      => 'strmadmin.xmass_queue:xmass_queue_subscriber',

                         namespace => DBMS_AQ.NAMESPACE_AQ,

                         callback  => 'plsql://strmadmin.dequeue_xmass_callback_proc?PR=0',

                         context   => HEXTORAW('FF'),

                         qosflags  => DBMS_AQ.NTFN_QOS_RELIABLE,

                         timeout   => 0

                         );

         reg_list := SYS.AQ$_REG_INFO_LIST(reginfo);

         DBMS_AQ.REGISTER (

             reg_list  => reg_list,

             reg_count => 1 );

      END;

      /

       

      Hope my problem is understood

       

      Regards, Sadock