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.
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;