Oracle Advance Queue - Dequeue not working

Create a code block and run the following:

DECLARE  dequeue_options      DBMS_AQ.dequeue_options_t;  message_properties   DBMS_AQ.message_properties_t;  message_handle       RAW (16);  I_PAYLOAD            ITEM_EVENT;  no_messages exception;  msg_content          VARCHAR2 (4000);  PRAGMA EXCEPTION_INIT (no_messages, -25228);BEGIN  dequeue_options.wait := DBMS_AQ.NO_WAIT;  dequeue_options.consumer_name := 'ITEM_SUBSCRIBER_1';     dequeue_options.navigation := DBMS_AQ.FIRST_MESSAGE;LOOP     DBMS_AQ.DEQUEUE (queue_name           => 'ITEM_EVENT_QUEUE',                      dequeue_options      => dequeue_options,                      message_properties   => message_properties,                      payload              => I_PAYLOAD,                      msgid                => message_handle                     );END LOOP;  EXCEPTION  WHEN no_messages  THEN     DBMS_OUTPUT.PUT_LINE ('No more messages left');END;

Let me know what happens to your enqueued messages.

You should have a table where you're dequing the data.

Can you also try adding the enqueud table in the agent and then specify the agent to the dequeue table.

DECLARE  aSubscriber sys.aq$_agent;BEGIN   aSubscriber := sys.aq$_agent('ITEM_SUBSCRIBER_1',                          'ITEM_EVENT_QUEUE',                          0);  dbms_aqadm.add_subscriber ( queue_name     => 'ITEM_EVENT_QUEUE'  ,subscriber     => aSubscriber);END;/

We faced a related problem (at least related to the title), we couldn't dequeue messages with a delay. The messages in the queue stayed the state "WAITING". And were not changed to "READY".

The Oracle AQ monitoring process that is responsable for changing the state from "WAITING" to "READY" (after the delay is expired) wasn't working properly.

For us a database restart fixed this issue.

I faced the same problem, but it was solved after changing these 2 DB parameters:

1. job_queue_processes (must be > than 0)
2. aq_tm_processes (autotuning)

Hope it helps.