This discussion is archived
2 Replies Latest reply: Sep 21, 2012 1:17 PM by 758358 RSS

Messages in Ready state are not dequeuing.

938260 Newbie
Currently Being Moderated
Messages are being left in a "Ready" state. Everything was working fine in 10g. However there was an upgrade to 11g and now queues have stop working. It is a multiconsumer queue with one subscriber. I can manually dequeue messages but the subscriber call back does not seem to function.

-- Create and start queues and queue_table
BEGIN

     DBMS_AQADM.CREATE_QUEUE_TABLE (
          queue_table => 'muse.m_en_queue_table',
          queue_payload_type => 'muse.eu_logid_type',
          multiple_consumers => TRUE
     );
     DBMS_AQADM.CREATE_QUEUE (
          queue_name => 'xxxxx.m_en_queue',
          queue_table => 'xxxxx.m_en_queue_table'
     );
     
     
     
     
     DBMS_AQADM.START_QUEUE('xxxxx.m_en_queue');
     
     --Start Default Exception Queue
     DBMS_AQADM.START_QUEUE('xxxxx.AQ$_M_EN_QUEUE_TABLE_E', enqueue => FALSE, dequeue => TRUE);
     
     
     
     DBMS_AQADM.ALTER_QUEUE (
          queue_name => 'xxxxx.m_en_queue',
          max_retries => 2,
retry_delay => 10);
END;
/
--Procedure to dequeue messages and try to resend to the notification service
CREATE OR REPLACE PROCEDURE muse.m_en_queue_callback_procedure(
     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);
     o_payload      muse.eu_logid_type;
     change_rec           muse.eocd_update%rowtype;
BEGIN
dequeue_options.WAIT := DBMS_AQ.no_wait;
dequeue_options.msgid := descr.msg_id;
dequeue_options.consumer_name := descr.consumer_name;

DBMS_AQ.DEQUEUE(
queue_name => descr.queue_name,
dequeue_options => dequeue_options,
message_properties => message_properties,
payload => o_payload,
msgid => message_handle
     );

...
...

END;
/


--Subscribed to queue
BEGIN
DBMS_AQADM.ADD_SUBSCRIBER (
queue_name => 'xxxxx.m_en_queue',
subscriber => SYS.AQ$_AGENT(
'muse_en_queue_subscriber',
NULL,
NULL )
);

DBMS_AQ.REGISTER (
SYS.AQ$_REG_INFO_LIST(
SYS.AQ$_REG_INFO(
'xxxxx.M_EN_QUEUE:MUSE_EN_QUEUE_SUBSCRIBER',
DBMS_AQ.NAMESPACE_AQ,
'plsql://xxxxx.m_en_queue_callback_procedure',
HEXTORAW('FF')
)
),
1
);
END;
/

Legend

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