DBMS_AQ.REMOVE issue

From: <z1hou1_at_gmail.com>
Date: 30 May 2007 08:38:08 -0700
Message-ID: <1180539488.628542.188260@u30g2000hsc.googlegroups.com>

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 Received on Wed May 30 2007 - 10:38:08 CDT