Advanced Queue questions

From: Boyle, Christopher J. <Christopher.J.Boyle_at_espn.com>
Date: Tue, 14 Dec 2004 21:08:01 -0500
Message-ID: <8FE22CA2A160D54A8B34B11713F31BB41213F5A1@xchgbrsm3.corp.espn.pvt>

Oracle 9.2

I have tried running the sample code I found at http://asktom.oracle.com/pls/ask/f?p=3D4950:8:2550868313853446564::NO::F4= 9
50_P8_DISPLAYID,F4950_P8_CRITERIA:8760267539329 ( a portion of which I have also included below)

for registering a pl/sql procedure to be triggered by a queue but I cannot seem to get it to work. The information gets loaded into the queue but then nothing happens. I have rtfm, tahited and googled and all I seem to find is the same example code but no suggestion on how to verify that the registered procedure is actually being called (outside of the fact that no data is inserted ) or if it is generating an error. Could someone make any suggestion as to what I may be doing wrong? Thanks
Chris

@ORA920> create or replace procedure notifyCB( context raw,

  2                                        reginfo sys.aq$_reg_info,
  3                                        descr sys.aq$_descriptor,
  4                                        payload raw,
  5                                        payloadl number)

  6 as

  7   dequeue_options dbms_aq.dequeue_options_t;
  8   message_properties dbms_aq.message_properties_t;
  9   message_handle RAW(16);

 10 message aq.message_typ;
 11 BEGIN

 12     dequeue_options.msgid :=3D descr.msg_id;
 13     dequeue_options.consumer_name :=3D descr.consumer_name;
 14     DBMS_AQ.DEQUEUE(queue_name =3D> descr.queue_name,
 15                     dequeue_options =3D> dequeue_options,
 16                     message_properties =3D> message_properties,
 17                     payload =3D> message,
 18                     msgid =3D> message_handle);
 19     insert into message_table values
 20     ( 'Dequeued and processed "' || message.text || '"' );
 21     COMMIT;

 22 END;
 23 /
Procedure created.

aq_at_ORA920> begin

  2      dbms_aqadm.add_subscriber
  3      ( queue_name =3D> 'aq.msg_queue',
  4        subscriber =3D> sys.aq$_agent( 'recipient', null, null ) );

  5 end;
  6 /
PL/SQL procedure successfully completed.

aq_at_ORA920> BEGIN

  2      dbms_aq.register
  3      ( sys.aq$_reg_info_list(
  4          sys.aq$_reg_info('AQ.MSG_QUEUE:RECIPIENT',
  5                            DBMS_AQ.NAMESPACE_AQ,
  6                           'plsql://AQ.notifyCB',
  7                            HEXTORAW('FF')) ) ,
  8        1 );

  9 end;
 10 /=20

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Dec 14 2004 - 20:03:44 CST