oracle-databaseplsqladvanced-queuing

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


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.


Solution

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