Re: DBMS_AQ.REMOVE issue

On May 30, 4:12 pm, DA Morgan <damor..._at_psoug.org> wrote:
> 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
>
> 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
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

Thanks Daniel for your reply. Why is it then that when we have a single consumer queue, that the dequeue operation physically deletes the row from the queue table? It is this characteristic that confused me and prompted me to post the earlier question. Why does the same operation on a multiple consumer queue not result in an empty queue table after the dequeue operation.

Regards,
z1hou1 Received on Wed May 30 2007 - 16:21:57 CDT