13 Replies Latest reply on Jan 26, 2015 1:19 PM by WGabriel

    Why are messages not dequeuing and stuck in the ready state?

    2830781

      Messages are successfully enqueueing but not dequeuing and are stuck in ready state (STATE = 0).  The ENQ_TIME is 5 hours ahead of system time.  In one environment, AQ is working (10g 10.2.0.4.0).  In the other environment, it is not working (11g 11.2.0.3.0).

       

      I just did the following:

       

      1. Purged queue table

      2. Stopped queues

      3. Dropped queues

      4. Dropped queue table

      5. Created queue table

      6. Created queues

      7. Started queues

       

      I tested once and a record was inserted in the queue table:

       

      MSGID      <msgid>

      CORRID     

      PRIORITY      1

      STATE      0

      DELAY     

      EXPIRATION     

      TIME_MANAGER_INFO     

      LOCAL_ORDER_NO      0

      CHAIN_NO      0

      CSCN      0

      DSCN      0

      ENQ_TIME      12/23/2014 4:33:43.338902 PM

      ENQ_UID      <enq_uid>

      ENQ_TID      <enq_tid>

      DEQ_TIME     

      DEQ_UID     

      DEQ_TID     

      RETRY_COUNT      0

      EXCEPTION_QSCHEMA     

      EXCEPTION_QUEUE     

      STEP_NO      0

      RECIPIENT_KEY      0

      DEQUEUE_MSGID     

      SENDER_NAME     

      SENDER_ADDRESS     

      SENDER_PROTOCOL     

      USER_DATA      <user_data>

      USER_PROP     

       

      Notice the RETRY_COUNT is 0.  The ENQ_TIME is 5 hours ahead.  In the procedures to enqueue and dequeue, there are no errors.

       

      Following is the plsql to enqueue:

       

      CREATE OR REPLACE PACKAGE BODY
      pkg_2
      AS
          FUNCTION queue_create_thing ( <parameters> )
              RETURN NUMBER
          IS
              enqueue_options     dbms_aq.enqueue_options_t;
              message_properties  dbms_aq.message_properties_t;
              message_handle      RAW(16);
              v_message           msg_type;
              v_thing_id          things.id%TYPE;
          BEGIN
      
              v_message := msg_type( <parameters> );
              dbms_aq.enqueue(queue_name => '<queue name>',
                              enqueue_options => enqueue_options,
                              message_properties => message_properties,
                              payload => v_message,
                              msgid => message_handle);
      
              RETURN v_thing_id;
      
          EXCEPTION
      
              WHEN OTHERS
              THEN
      
                 errpkg.record_and_stop (SQLCODE);
      
          END queue_create_thing;
      
          PROCEDURE queue_delete_thing( <parameters> )
          IS
              enqueue_options     dbms_aq.enqueue_options_t;
              message_properties  dbms_aq.message_properties_t;
              message_handle      RAW(16);
              v_message           msg_type;
          BEGIN
      
              v_message := msg_type( <parameters> );
              dbms_aq.enqueue(queue_name => '<queue name>',
                              enqueue_options => enqueue_options,
                              message_properties => message_properties,
                              payload => v_message,
                              msgid => message_handle);
      
          END;
      END pkg_2;
      

       

      Following is the code to dequeue:

      CREATE OR REPLACE PACKAGE BODY
      pkg_1
      AS  
      
          PROCEDURE create_thing ( context IN RAW,
                                      reginfo IN sys.aq$_reg_info,
                                      descr IN sys.aq$_descriptor,
                                      payload IN RAW,
                                      payloadl IN NUMBER )
          IS  
              dequeue_options dbms_aq.dequeue_options_t;
              message_properties dbms_aq.message_properties_t;
              message_handle RAW(16);
              message msg_type;
          BEGIN
      
              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 => message,
                              msgid => message_handle);
      
              pkg_2.create_thing( p_thing_id => message.thing_id );
        
              UPDATE table t
                 SET creation_complete = 1
               WHERE id = message.thing_id;
      
              COMMIT;
      
          EXCEPTION
              WHEN OTHERS
              THEN
      
                  ROLLBACK;
         
                  plog.error(SQLERRM);
                  plog.full_call_stack;
                          
          END create_thing;
          
          
          PROCEDURE delete_thing ( context IN RAW,
                                      reginfo IN sys.aq$_reg_info,
                                      descr IN sys.aq$_descriptor,
                                      payload IN RAW,
                                      payloadl IN NUMBER )
          IS  
              dequeue_options dbms_aq.dequeue_options_t;
              message_properties dbms_aq.message_properties_t;
              message_handle RAW(16);
              message msg_type;
          BEGIN
      
              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 => message,
                              msgid => message_handle);
      
              pkg_2.delete_thing( p_thing_id => message.thing_id );
        
              COMMIT;
      
          EXCEPTION
              WHEN OTHERS
              THEN
      
                  ROLLBACK;
          
                  plog.error(SQLERRM);
                  plog.full_call_stack;
                          
          END delete_thing;    
      
      END pkg_1;