that -- is what AQ is about.  It does the "at least once, at most once" processing.  AQ is all about allowing multiple consumers feed off of a single queue table -- at the same time, without blocking.

this is what AQ is <b>all about</b>....

Do this (stolen right from the AQ app developers guide basically)

set echo on

connect / as sysdba

DROP USER aq CASCADE;
CREATE USER aq IDENTIFIED BY aq;
GRANT EXECUTE ON DBMS_AQADM TO aq;
GRANT Aq_administrator_role TO aq;
GRANT CONNECT, RESOURCE TO aq;
GRANT EXECUTE ON dbms_aq TO aq;
connect aq/aq


CREATE type aq.Message_typ as object ( subject VARCHAR2(30), text VARCHAR2(80));
/


EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE ( queue_table => 'aq.objmsgs80_qtab', queue_payload_type =>  'aq.Message_typ' );
EXECUTE DBMS_AQADM.CREATE_QUEUE ( queue_name => 'MSG_QUEUE', queue_table => 'aq.objmsgs80_qtab');
EXECUTE DBMS_AQADM.START_QUEUE (  queue_name => 'MSG_QUEUE');

create procedure enqueue_msg( p_msg in varchar2 )
as
 enqueue_options dbms_aq.enqueue_options_t;
 message_properties dbms_aq.message_properties_t;
 message_handle RAW(16);
 message aq.message_typ;
BEGIN
   message := message_typ('NORMAL MESSAGE',  p_msg );
   dbms_aq.enqueue(queue_name => 'msg_queue', enqueue_options => enqueue_options,
                   message_properties => message_properties, payload => message,
                   msgid => message_handle);
   COMMIT;
end;
/

create procedure dequeue_msg
as
 dequeue_options dbms_aq.dequeue_options_t;
 message_properties dbms_aq.message_properties_t;
 message_handle RAW(16);
 message aq.message_typ;
BEGIN
   DBMS_AQ.DEQUEUE(queue_name => 'msg_queue', dequeue_options => dequeue_options,
                   message_properties => message_properties,
                   payload => message, msgid => message_handle);

   DBMS_OUTPUT.PUT_LINE ('Message: ' || message.subject || ' ... ' || message.text );
   COMMIT;
END;
/


Ok, now, open 3 separate windows.  I'm using unix (linux) so I wrote a small script:

[tkyte@tkyte-pc-isdn tkyte]$ cat dq.csh
#!/bin/csh -f

while (1)
sqlplus aq/aq <<EOF
exec dequeue_msg
exit
EOF
end
[tkyte@tkyte-pc-isdn tkyte]$


In 2 of the 3 windows -- I ran that.  They each block on the dequeue.

Now, in the other window do this:


sqlplus aq/aq

SQL> exec enqueue_msg( 'Hello' );


and you'll see ONE of them get it, process it and go back for more.  Do it again and you'll probably see the OTHER one get it, process it and go back for more (or the first one will get it again).

Now, put that into a loop

begin
   for i in 1 .. 50
   loop
     equeue_msg( 'hello ' || i );
   end loop;
end;
/


and just watch them goto town.



This is what AQ does!



 

declare
   g_queue               varchar2(30) := 'MY_QUEUE';
   g_queue_array_size    int := 50;
   g_timeout             int := 60;
   l_message             my_array;
   l_message_properties  dbms_aq.message_properties_array_t;

   l_dequeue_options     dbms_aq.dequeue_options_t;
   l_message_properties  dbms_aq.message_properties_array_t := dbms_aq.message_properties_array_t();
   l_message             my_array := my_array();
   l_message_cnt         pls_integer;
   l_msgid               dbms_aq.msgid_array_t := dbms_aq.msgid_array_t();

   e_queue_timeout    exception;
   pragma exception_init(e_queue_timeout,-25228);

begin
   p_message_properties := l_message_properties;
   p_message            := l_message;
   
   l_message.extend(g_queue_array_size);
   l_message_properties.extend(g_queue_array_size);

   l_dequeue_options.wait := nvl(g_timeout,10);
   l_dequeue_options.navigation := dbms_aq.first_message;

   l_message_cnt :=
       dbms_aq.dequeue_array(
                   queue_name               => g_queue,
                   dequeue_options          => l_dequeue_options,
                   array_size               => g_queue_array_size,
                   message_properties_array => l_message_properties,
                   payload_array            => l_message,
                   msgid_array              => l_msgid);

exception
   when e_queue_timeout then
      ...   
end;