Oracle Advanced Queuing allows for consumers to wait indefinitely for a new message to arrive. I'd like to see the API change so that even if WAIT FOREVER is specified (or any time limit really) AQ could raise ORA-25228 (no more messages waiting) to better manage the resources of the system.
Some cases where an infinetly waiting listener could cause issues (or have already are):
- The listener has been waiting for an extraordinary amount of time without receiving a message and is blocking other queue workers from running (Oracle Doc ID 2001165.1). The DBA could set an init_param that would specifiy the longest amount of time a job may sit there waiting (say 86400 seconds for a default)
- AQ may have the same callback running in multiple AQ$_PLSQL_NTFN_% jobs. Future versions of the resource manager could indicate the system is under stress and AQ would "lie" to all but one job running the same notification callback and report that there are no more messages. The same could happen under other circumstances (PGA getting to high with PL/SQL or Java in the DB callbacks, etc)
Example of how a worker would need to be written to handle this:
procedure work_messages
as
pragma autonomous_transaction;
dequeue_options_l dbms_aq.dequeue_options_t;
message_id_l raw (16);
message_l payload_t;
message_properties_l dbms_aq.message_properties_t;
no_more_messages_ex exception;
pragma exception_init (no_more_messages_ex, -25228);
begin
dequeue_options_l.wait := dbms_aq.forever;
dequeue_options_l.navigation := dbms_aq.first_message;
while (true) loop -- way out is no_more_messages_ex
dbms_aq.dequeue (queue_name => queue_name_c,
dequeue_options => dequeue_options_l,
message_properties => message_properties_l,
payload => message_l,
msgid => message_id_l);
work_message (message_l);
commit;
end loop;
exception
when no_more_messages_ex
then
null;
end work_messages;