This discussion is archived
3 Replies Latest reply: Dec 2, 2013 4:52 AM by user3837624 RSS

Raise a callback procedure on an aq$_jms_text_message payload type

user3837624 Newbie
Currently Being Moderated

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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points