PL/SQL: How to handle timeout for DBMS_AQ.DEQUEUE

Question

I am calling DBMS_AQ.DEQUEUE from a PL/SQL program. I don't want to wait forever but regularly timeout if there is no data in the queue before I try dequeuing again. The Oracle documentation for the DEQUEUE procedure is clear on how to specify the timeout (using the wait field in dequeue_options). It does however make no mention of what happens in case of a timeout.

I would have expected the documentation to specify how to handle the timeout case but either I overlooked it, it's a documentation oversight or it should be obvious and it's just my lack of PL/SQL experience that makes me not figure out how to do it. In any case any advice on how to best handle / catch a DEQUEUE timeout would be much appreciated.

What do you mean by "handle the timeout"? When you get your `ORA-25228` and catch it, can't you just try the dequeue again? — ninesided, Dec 03 '15 at 08:43
OK, maybe that's the answer already. The documentation doesn't mention ORA-25228. I would have expected it to tell me which exceptions are thrown in which case, like e.g. in the Java docs. I'll give that a try. — themik81, Dec 03 '15 at 08:47

score 2 · Accepted Answer · answered Dec 04 '15 at 07:27

For anyone else, who's looking for an answer to this, this here is what I was after (thanks to ninesided's comment):

begin
  dbms_aq.dequeue(
    payload            => payload
   ,queue_name         => queue_name
   ,dequeue_options    => deq_opt
   ,message_properties => msg_prty
   ,msgid              => msgid
  );
exception
  when others then
    if sqlcode = -25228 then
      -- handle timeout here
    end if;
end;

PL/SQL: How to handle timeout for DBMS_AQ.DEQUEUE

1 Answers1