2 Replies Latest reply: Sep 21, 2012 3:17 PM by 758358 RSS

    Messages in Ready state are not dequeuing.

    938260
      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;
      /