Re: DBMS_AQ.REMOVE issue

z1hou1_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

The purpose of the queue table includes an audit trail of messages. If .REMOVE removed the messages your audit trail would evaporate. The product is doing precisely what the documentation indicates that it should.

If you want a DELETE statement then you should write that into your application code.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org