Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> DELAY not working with Queues??
I was wondering if anyone else has had this problem or not:
I've reproduced this "problem" on both Oracle 8i and 10g ... (Current version is 10.2.0.2.0)
I setup a basic, single-consumer queue .. ie the most "vanilla" queue you can do ... nothing special.
I then enqueue an item ... no delay ... (commit, of course) ... I can then dequeue it no problem.
However, as soon as I add a delay (say 10 second), the item just sits in the queue forever ... I can see it, and its status is always "waiting" (ie value = 1 )
Yes, I have tried setting the aq_tm_processes value to > 0 ... doesn't do anything ... Oracle Support confirms that this setting - in 10g - is "auto-tuned", so no need to touch it in 10g though ...
Here's my test scripts:
--Create the queue
exec dbms_aqadm.create_queue('test_queue','test_table')
v_mesg_hndl raw(16); v_message message_type; v_enqueue_options dbms_aq.enqueue_options_t; v_message_properties dbms_aq.message_properties_t;
begin
v_message := message_type(1,'001',1,'K','This is a test message');
dbms_aq.enqueue(queue_name => 'test_queue',
enqueue_options => v_enqueue_options,
message_properties => v_message_properties,
payload => v_message,
msgid => v_mesg_hndl);
commit;
end;
/
v_mesg_hndl raw(16); v_message message_type; v_enqueue_options dbms_aq.enqueue_options_t; v_message_properties dbms_aq.message_properties_t;
begin
v_message := message_type(1,'001',1,'K','This is a test message'); v_message_properties.delay := 10;
dbms_aq.enqueue(queue_name => 'test_queue',
enqueue_options => v_enqueue_options,
message_properties => v_message_properties,
payload => v_message,
msgid => v_mesg_hndl);
commit;
end;
/
v_dequeue_options dbms_aq.dequeue_options_t; v_message_properties dbms_aq.message_properties_t; v_message_id raw(16); v_message message_type;
no_error_entries_e EXCEPTION;
PRAGMA EXCEPTION_INIT (no_error_entries_e, -25228);
begin
v_dequeue_options.wait := dbms_aq.no_wait;
loop
dbms_aq.dequeue
( 'TEST_QUEUE',
v_dequeue_options, v_message_properties, v_message, v_message_id );
|| '", application_id = ' || v_message.application_id || ', another_id = ' || v_message.another_id || ', id = ' || v_message.id || ', status = '|| v_message.status
commit;
end loop;
exception
when no_error_entries_e then
dbms_output.put_line('No more messages');
commit;
when others then
dbms_output.put_line('The error code was = ' || SQLCODE);
dbms_output.put_line('The error message was ' || SQLERRM);
commit;
end;
/
column name format a20 trunc
column owner format a10 trunc
select owner, name, waiting, ready, expired
from dba_queues, v$aq
where dba_queues.qid = v$aq.qid and owner = 'MY_ID'
/
SQL > @status
OWNER NAME WAITING READY EXPIRED
---------- -------------------- ---------- ---------- ----------
MY_ID TEST_QUEUE 2 0 0
MY_ID AQ$_TEST_TABLE_E 0 0 0
2 rows selected.
That message will sit there for days ... Oracle support is unable to reproduce ... I was hoping to get some other ideas from others out there ...
uh .. "help?" ;) Received on Tue Nov 14 2006 - 10:38:12 CST