Oracle AQ. Error ORA-25263: no message in queue when dequeue message

Question

I want to dequeue message with message id from my queue table, but I get error that ORA-25263: no message in queue OPERATION_QUEUE with message ID DBF9DE01CB5C0DA7E0550000FF000001

How I can dequeue message in wait state, if when I do select select * from AQ$operation_queue_table; Message with same msgid is exist.

declare
  l_msg_id raw(16);
  l_deq_opt dbms_aq.dequeue_options_t;
  l_msg_prop dbms_aq.message_properties_t;
  l_payload SYS.AQ$_JMS_TEXT_MESSAGE;
  no_messages exception;
  PRAGMA EXCEPTION_INIT (no_messages, -25228);
begin
  l_deq_opt.wait := DBMS_AQ.NO_WAIT;
  l_deq_opt.navigation := DBMS_AQ.FIRST_MESSAGE;
  l_deq_opt.dequeue_mode := DBMS_AQ.REMOVE_NODATA;
  select msg_id into l_deq_opt.msgid from AQ$OPERATION_QUEUE_TABLE where corr_id = 20220304162829028090113812101;

LOOP
    dbms_aq.dequeue(
      queue_name         => 'OPERATION_QUEUE',
      dequeue_options    => l_deq_opt,
      message_properties => l_msg_prop,
      payload            => l_payload,
      msgid              => l_msg_id
    );
END LOOP ;
 EXCEPTION
  WHEN no_messages
  THEN
     DBMS_OUTPUT.PUT_LINE ('No more messages left');
end; 

Answer 1

Your message is in WAIT state, which means the message was enqueued with a delay > 0. It is thus not available for consumption yet. Once delay time expires, the message should become READY for consumption, and then you will be able to dequeue it.