title: Oracle Advance Queue - Dequeue not working
body: <p>I can't seem to find the solution to my problem, I've been stuck at this for hours.</p>
  
  <p>I'm usings Oracle AQs:</p>
  
  <pre><code>       Dbms_Aqadm.Create_Queue_Table(Queue_Table        =&gt; 'ITEM_EVENT_QT',
                                      Queue_Payload_Type =&gt; 'ITEM_EVENT',
                                      Multiple_Consumers =&gt; TRUE);
  
         Dbms_Aqadm.Create_Queue(Queue_Name          =&gt; 'ITEM_EVENT_QUEUE',
                                Queue_Table         =&gt; 'ITEM_EVENT_QT',
                                Max_Retries         =&gt; 5,
                                Retry_Delay         =&gt; 0,
                                Retention_Time      =&gt; 432000, -- 5 DAYS
                                Dependency_Tracking =&gt; FALSE,
                                COMMENT             =&gt; 'Item Event Queue');
         -- START THE QUEUE
         Dbms_Aqadm.Start_Queue('ITEM_EVENT_QUEUE');
         -- GRANT QUEUE PRIVILEGES
         Dbms_Aqadm.Grant_Queue_Privilege(Privilege    =&gt; 'ALL',
                                         Queue_Name   =&gt; 'ITEM_EVENT_QUEUE',
                                         Grantee      =&gt; 'PUBLIC',
                                         Grant_Option =&gt; FALSE);
      END;
  </code></pre>
  
  <p>Here's one of my subscribers:</p>
  
  <pre><code>Dbms_Aqadm.Add_Subscriber(Queue_Name =&gt; 'ITEM_EVENT_QUEUE',
                              Subscriber =&gt; Sys.Aq$_Agent('ITEM_SUBSCRIBER_1',
                                                          NULL,
                                                          NULL),
                              rule   =&gt;  'tab.user_data.header.thread_no = 1');
  
     Dbms_Aq.Register(Sys.Aq$_Reg_Info_List(Sys.Aq$_Reg_Info('ITEM_EVENT_QUEUE:ITEM_SUBSCRIBER_1',
                                                            Dbms_Aq.Namespace_Aq,
                                                            'plsql://ITEM_API.GET_QUEUE_FROM_QUEUE',
                                                            HEXTORAW('FF'))),1);
  </code></pre>
  
  <p>The subscriber registration:</p>
  
  <p>Whenever a certain event occurs on my DB, I'm using a trigger to add "the event" to my AQ by calling the following procedure from my <code>ITEM_API</code> package:</p>
  
  <pre><code>  PROCEDURE ADD_EVENT_TO_QUEUE(I_EVENT       IN ITEM_EVENT,
                                 O_STATUS_CODE OUT VARCHAR2,
                                 O_ERROR_MSG   OUT VARCHAR2) IS
  
      ENQUEUE_OPTIONS    DBMS_AQ.ENQUEUE_OPTIONS_T;
      MESSAGE_PROPERTIES DBMS_AQ.MESSAGE_PROPERTIES_T;
      MESSAGE_HANDLE     RAW(16);
      EVENT              ITEM_EVENT;
      HEADER_PROP        HEADER_PROPERTIES;
    BEGIN
      EVENT                              := I_EVENT;
      EVENT.SEQ_NO                       := ITEM_EVENT_SEQ.NEXTVAL;
      ENQUEUE_OPTIONS.VISIBILITY         := DBMS_AQ.ON_COMMIT;
      ENQUEUE_OPTIONS.SEQUENCE_DEVIATION := NULL;
      MESSAGE_PROPERTIES.PRIORITY        := 1;
      MESSAGE_PROPERTIES.DELAY           := DBMS_AQ.NO_DELAY;
      MESSAGE_PROPERTIES.EXPIRATION      := DBMS_AQ.NEVER;
      HEADER_PROP                        := HEADER_PROPERTIES(1);
      EVENT.HEADER                       := HEADER_PROP;
      DBMS_AQ.ENQUEUE(QUEUE_NAME         =&gt; 'ITEM_EVENT_QUEUE',
                      ENQUEUE_OPTIONS    =&gt; ENQUEUE_OPTIONS,
                      MESSAGE_PROPERTIES =&gt; MESSAGE_PROPERTIES,
                      PAYLOAD            =&gt; EVENT,
                      MSGID              =&gt; MESSAGE_HANDLE);
    EXCEPTION
      WHEN OTHERS THEN
        ERROR_HANDLER.LOG_ERROR(NULL,
                                EVENT.ITEM,
                                EVENT.SEQ_NO,
                                SQLCODE,
                                SQLERRM,
                                O_STATUS_CODE,
                                O_ERROR_MSG);
        RAISE;
    END ADD_EVENT_TO_QUEUE;
  </code></pre>
  
  <p>And it's working because when I check my AQ table, I can find "the event", however my dequeue method is not dequeing, as you can see in the image bellow, there's no <code>DEQ_TIME</code>.</p>
  
  <p>Here's my dequeue method, also from my <code>ITEM_API</code> package:</p>
  
  <pre><code>  PROCEDURE GET_QUEUE_FROM_QUEUE(CONTEXT  RAW,
                                   REGINFO  SYS.AQ$_REG_INFO,
                                   DESCR    SYS.AQ$_DESCRIPTOR,
                                   PAYLOAD  RAW,
                                   PAYLOADL NUMBER) IS
  
      R_DEQUEUE_OPTIONS    DBMS_AQ.DEQUEUE_OPTIONS_T;
      R_MESSAGE_PROPERTIES DBMS_AQ.MESSAGE_PROPERTIES_T;
      V_MESSAGE_HANDLE     RAW(16);
      I_PAYLOAD            ITEM_EVENT;
      L_PROC_EVENT         BOOLEAN;
      O_TARGETS            CFG_EVENT_STAGE_TBL;
      O_ERROR_MSG          VARCHAR2(300);
      O_STATUS_CODE        VARCHAR2(100);
    BEGIN
      R_DEQUEUE_OPTIONS.MSGID         := DESCR.MSG_ID;
      R_DEQUEUE_OPTIONS.CONSUMER_NAME := DESCR.CONSUMER_NAME;
      R_DEQUEUE_OPTIONS.DEQUEUE_MODE  := DBMS_AQ.REMOVE;
      --R_DEQUEUE_OPTIONS.WAIT          := DBMS_AQ.NO_WAIT;
      DBMS_AQ.DEQUEUE(QUEUE_NAME         =&gt; DESCR.QUEUE_NAME,
                      DEQUEUE_OPTIONS    =&gt; R_DEQUEUE_OPTIONS,
                      MESSAGE_PROPERTIES =&gt; R_MESSAGE_PROPERTIES,
                      PAYLOAD            =&gt; I_PAYLOAD,
                      MSGID              =&gt; V_MESSAGE_HANDLE);
      IF I_PAYLOAD IS NOT NULL THEN
        L_PROC_EVENT := PROCESS_EVENT(I_PAYLOAD,
                                      O_TARGETS,
                                      O_STATUS_CODE,
                                      O_ERROR_MSG);
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        ERROR_HANDLER.LOG_ERROR(NULL,
                                NULL,
                                NULL,
                                SQLCODE,
                                SQLERRM,
                                O_STATUS_CODE,
                                O_ERROR_MSG);
        RAISE;
    END GET_QUEUE_FROM_QUEUE;
  </code></pre>
  
  <p>Am I doing something wrong? How can I fix this? I think there might be a problem with my subscriber registration, but I'm not sure.</p>
  
  <p><strong>EDIT:</strong> I've just figured out that if I remove the subscribers and the register, and then re-add them, they'll dequeue all messages. Howerver if another event gets enqueued, it stays there indefinetly (or until I remove and add the subscribers again):</p>
  
  <p>The record with state 0 and no <code>DEQ_TIME</code> is the new one.</p>
  
  <p>Do I need a scheduler or something like that?</p>
  
  <p><strong>EDIT:</strong> I've added a scheduler propagation to my AQ:</p>
  
  <pre><code>DBMS_AQADM.SCHEDULE_PROPAGATION('ITEM_EVENT_QUEUE');
  </code></pre>
  
  <p>and even added the next_time field:</p>
  
  <pre><code>DBMS_AQADM.SCHEDULE_PROPAGATION('ITEM_EVENT_QUEUE', SYSDATE + 30/86400);
  </code></pre>
  
  <p>Still doesn't work. Any suggestions? I guess the AQ Notifications aren't working, and my callback procedure is never called. How can I fix this?</p>
  
  <p><strong>EDIT:</strong> I've removed my procedure from the package just for testing purposes, so my team mates can compile the ITEM_API package (I don't know if recompiling the package, may or may not have impacts on the dequeue process).
  Still doesn't work.</p>