CODEWITHSUNDEEP
oracle-databaseplsqladvanced-queuing
themik81
themik81

Reputation: 401

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.

Upvotes: 3

Views: 3602

Answers (1)

themik81
themik81

Reputation: 401

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;

Upvotes: 2

Related Questions