Re: DBMS_AQ.REMOVE issue

On May 30, 11:38 am, z1h..._at_gmail.com wrote:
> Hi,
> When I dequeue a message from the queue using the DBMS_AQ.REMOVE
> option, the message still stays in the queue table (test_qtbl) long
> after the dequeue is complete. I am under the impression that this
> option will remove the message from the queue table when the message
> is dequeued. I have also specified the retention time while creating
> the queue to zero (not to retain any messages after dequeue). After
> the dequeue, if I try to dequeue the same message again it is not
> available for dequeuing.
> Although this behavior is not affecting my application, it is very
> annoying to see the messages in the queue long after it has been
> processed.
>
> I have given a sample code below which demonstrates this. Am I missing
> something here?
>
> create or replace type test_typ as object (msg varchar2(5))
>
> begin
> dbms_aqadm.create_queue_table(queue_table => 'test_qtbl',
> queue_payload_type => 'test_typ', multiple_consumers => true);
> dbms_aqadm.create_queue(queue_name => 'test_queue', queue_table =>
> 'test_qtbl', retention_time => 0);
> dbms_aqadm.start_queue('test_queue');
> end;
>
> declare
> l_queue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
> l_message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
> l_message_id RAW(16);
> l_type test_typ;
>
> l_recipient_list DBMS_AQ.AQ$_RECIPIENT_LIST_T;
> l_agent sys.aq$_agent := sys.aq$_agent('agent1', null, null);
> begin
> l_recipient_list(0) := l_agent;
> l_message_properties.recipient_list := l_recipient_list;
>
> l_type := test_typ('Help');
> dbms_aq.enqueue('test_queue', l_queue_options, l_message_properties,
> l_type, l_message_id);
> end;
>
> declare
> l_type test_typ;
> l_queue_options DBMS_AQ.DEQUEUE_OPTIONS_T;
> l_message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
> l_message_id RAW(16);
> begin
> l_queue_options.wait := 3;
> l_queue_options.dequeue_mode := DBMS_AQ.REMOVE;
> l_queue_options.consumer_name := 'agent1';
> dbms_aq.dequeue('test_queue', l_queue_options, l_message_properties,
> l_type, l_message_id);
> DBMS_OUTPUT.put_line(l_type.msg);
> end;
>
> Regards,
> z1hou1

Ok you have tested it and you cannot dequeue the same message more than once. It doesn't affect your application ( as you have noted ) but it just annoys you?

Why don't you submit this as a service request to oracle? Received on Wed May 30 2007 - 13:31:00 CDT