Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> DELAY not working with Queues??

DELAY not working with Queues??

From: <the_elementalist_at_hotmail.com>
Date: 14 Nov 2006 08:38:12 -0800
Message-ID: <1163522292.515958.94180@m73g2000cwd.googlegroups.com>


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;

/* This exception indicates that these are no more messages on queue */

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

dbms_output.put_line(
'The dequeued message is "' || v_message.message_text
|| '", 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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US