3 Replies Latest reply: Dec 2, 2013 6:52 AM by user3837624 RSS

    Raise a callback procedure on an aq$_jms_text_message payload type

    user3837624

      Hello,

       

      After few days of hard searching over the web and the Oracle documentation, I finally don't have succeded on raising the callback procedure on a queue (multi-consumers or not). The problem concerned only when I have sys.aq$_jms_text_message payload type (I've already succeded to raise the callback when the payload type is not aq_$jms_text).

      I'm using an OracleXE for test purpose.

       

      So have you ever implemeted such callback ? What can be wrong in the code ?

       

      Thanks for you help, Lionel.

       

       

      -- connect / as sysdba;

      -- create user jmsuser identified by jmsuser;

      -- Grant connect, resource to jmsuser;

      -- Grant aq_user_role TO jmsuser;

      -- Grant execute ON sys.dbms_aqadm TO jmsuser;

      -- Grant execute ON sys.dbms_aq TO jmsuser;

      -- Grant execute ON sys.dbms_aqin TO jmsuser;

      -- Grant execute ON sys.dbms_aqjms TO jmsuser;

       

      -- connect as jmsuser/jmsuser

      begin

      DBMS_AQADM.stop_queue(queue_name => 'TEXTQUEUE') ;

      end ;

      /

      begin

      DBMS_AQADM.drop_queue(queue_name => 'TEXTQUEUE') ;

      end ;

      /

      begin

      DBMS_AQADM.drop_queue_table(queue_table => 'TEXTQUEUETABLE') ;

      end ;

      /

      begin

      DBMS_AQADM.create_queue_table(

        queue_table => 'TEXTQUEUETABLE',

        queue_payload_type => 'sys.aq$_jms_text_message',

        multiple_consumers => FALSE) ;

      end ;

      /

      begin

      DBMS_AQADM.create_queue(queue_name => 'TEXTQUEUE', queue_table => 'TEXTQUEUETABLE') ;

      end ;

      /

      begin

      DBMS_AQADM.start_queue(queue_name => 'TEXTQUEUE', enqueue => TRUE, dequeue => TRUE) ;

      end ;

      /

      drop table log

      /

      create table log (text varchar2(2000))

      /

      create or replace procedure PROCESS_MESSAGE_CALLBACK

      ( context  IN RAW,

        reginfo  IN SYS.AQ$_REG_INFO,

        descr    IN SYS.AQ$_DESCRIPTOR,

        payload  IN VARCHAR2,

        payloadl IN NUMBER);

      )

      is

      begin

      insert into log values ('notification received');

      commit;

      end PROCESS_MESSAGE_CALLBACK;

      /

      begin

      dbms_aq.register(

      SYS.AQ$_REG_INFO_LIST(

        SYS.AQ$_REG_INFO(

          'JMSUSER.TEXTQUEUE',

          DBMS_AQ.NAMESPACE_AQ,

          'plsql://JMSUSER.PROCESS_MESSAGE_CALLBACK?PR=1',

          HEXTORAW('FF')

        )

      ),1

      );

      end;

      /

      declare

        msg SYS.AQ$_JMS_TEXT_MESSAGE;

        msg_hdr SYS.AQ$_JMS_HEADER;

        msg_agent SYS.AQ$_AGENT;

        msg_proparray SYS.AQ$_JMS_USERPROPARRAY;

        msg_property SYS.AQ$_JMS_USERPROPERTY;

        queue_options DBMS_AQ.ENQUEUE_OPTIONS_T;

        msg_props DBMS_AQ.MESSAGE_PROPERTIES_T;

        msg_id RAW(16);

        dummy VARCHAR2(4000);

      begin

        msg_agent := SYS.AQ$_AGENT(' ', null, 0);

        msg_proparray := SYS.AQ$_JMS_USERPROPARRAY();

        msg_proparray.EXTEND(1);

        msg_property := SYS.AQ$_JMS_USERPROPERTY('JMS_OracleDeliveryMode', 100, '2', NULL, 27);

        msg_proparray(1) := msg_property;

        msg_hdr := SYS.AQ$_JMS_HEADER(msg_agent,null,'<USERNAME>',null,null,null,msg_proparray);

        msg := SYS.AQ$_JMS_TEXT_MESSAGE(msg_hdr,null,null,null);

        msg.text_vc := 'texte du message';

        msg.text_len := length(msg.text_vc);

        DBMS_AQ.ENQUEUE( queue_name => 'TEXTQUEUE'

                       , enqueue_options => queue_options

                       , message_properties => msg_props

                       , payload => msg

                       , msgid => msg_id);

      end;

      /

      select * from log;

        • 1. Re: Raise a callback procedure on an aq$_jms_text_message payload type
          Hugo_4711

          Hi,

           

          I think the problem is that you register your procedure with PR=1.  Try to register it with PR=0.

          PR=1 is used when your payload is XML. For a raw payload you should use PR=0.

          see  Oracle Streams AQ TYPEs

           

           

          I am using an Enterprise Edition and it works that way. It should also work on an XE.

          • 2. Re: Raise a callback procedure on an aq$_jms_text_message payload type
            user3837624

            Hi Hugo,

            I've tried with PR=0, but It's not working. So I'll try on a standard or enterprise edition.

            Thanks, Lionel.

            • 3. Re: Raise a callback procedure on an aq$_jms_text_message payload type
              user3837624

              Ok, now it's working ; using PR=0 or PR=1... It was just missing a "commit" during notification creation. Here is the final scripts for those who wanted to reproduce.

               

              Lionel.

               

              delete from log;

              commit;

              /

              begin

              DBMS_AQADM.stop_queue(queue_name => 'TEXTQUEUE') ;

              end ;

              /

              begin

              DBMS_AQADM.drop_queue(queue_name => 'TEXTQUEUE') ;

              end ;

              /

              begin

              DBMS_AQADM.drop_queue_table(queue_table => 'TEXTQUEUETABLE') ;

              end ;

              /

              begin

              DBMS_AQADM.create_queue_table(

                queue_table => 'TEXTQUEUETABLE',

                queue_payload_type => 'sys.aq$_jms_text_message',

                multiple_consumers => FALSE) ;

              end ;

              /

              begin

              DBMS_AQADM.create_queue(queue_name => 'TEXTQUEUE', queue_table => 'TEXTQUEUETABLE') ;

              end ;

              /

              begin

              DBMS_AQADM.start_queue(queue_name => 'TEXTQUEUE', enqueue => TRUE, dequeue => TRUE) ;

              end ;

              /

              drop table log

              /

              create table log (text varchar2(2000))

              /

              create or replace procedure PROCESS_MESSAGE_CALLBACK

              ( context  IN RAW,

                reginfo  IN SYS.AQ$_REG_INFO,

                descr    IN SYS.AQ$_DESCRIPTOR,

                payload  IN VARCHAR2,

                payloadl IN NUMBER

              )

              is

              begin

              insert into log values ('notification received');

              commit;

              end PROCESS_MESSAGE_CALLBACK;

              /

              begin

              dbms_aq.register(

              SYS.AQ$_REG_INFO_LIST(

                SYS.AQ$_REG_INFO(

                  'JMSUSER.TEXTQUEUE',

                  DBMS_AQ.NAMESPACE_AQ,

                  'plsql://JMSUSER.PROCESS_MESSAGE_CALLBACK?PR=1',

                  HEXTORAW('FF')

                )

              ),1

              );

              commit;

              end;

              /

              commit;

              /

              declare

                msg SYS.AQ$_JMS_TEXT_MESSAGE;

                msg_hdr SYS.AQ$_JMS_HEADER;

                msg_agent SYS.AQ$_AGENT;

                msg_proparray SYS.AQ$_JMS_USERPROPARRAY;

                msg_property SYS.AQ$_JMS_USERPROPERTY;

                queue_options DBMS_AQ.ENQUEUE_OPTIONS_T;

                msg_props DBMS_AQ.MESSAGE_PROPERTIES_T;

                msg_id RAW(16);

                dummy VARCHAR2(4000);

              begin

                msg_agent := SYS.AQ$_AGENT(' ', null, 0);

                msg_proparray := SYS.AQ$_JMS_USERPROPARRAY();

                msg_proparray.EXTEND(1);

                msg_property := SYS.AQ$_JMS_USERPROPERTY('JMS_OracleDeliveryMode', 100, '2', NULL, 27);

                msg_proparray(1) := msg_property;

                msg_hdr := SYS.AQ$_JMS_HEADER(msg_agent,null,'<USERNAME>',null,null,null,msg_proparray);

                msg := SYS.AQ$_JMS_TEXT_MESSAGE(msg_hdr,null,null,null);

                msg.text_vc := 'texte du message';

                msg.text_len := length(msg.text_vc);

                DBMS_AQ.ENQUEUE( queue_name => 'TEXTQUEUE'

                               , enqueue_options => queue_options

                               , message_properties => msg_props

                               , payload => msg

                               , msgid => msg_id);

                commit;

              end;

              /

              select * from log;