1 Reply Latest reply: Oct 3, 2013 9:19 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;