Re: Oracle AQ Question

declare

subscriber SYS.aq$_agent;

begin

subscriber := SYS.aq$_agent('INBOUND_DETAINEE', 'tsi_aq_user5.DETAINEES_INBOUND_AQ_at_tsi_aq_user5', null);

dbms_aqadm.add_subscriber(queue_name => 'DETAINEES_OUTBOUND_AQ',

subscriber => subscriber,

rule => 'tab.user_data.DTNEE_DTF_ID like ''E%''');

end;
/
 The schedule

EXEC DBMS_AQADM.SCHEDULE_PROPAGATION(Queue_name => 'DETAINEES_OUTBOUND_AQ',LATENCY=>0,Destination=>'tsi_aq_user5')

My alert logs have nothing of interest tody:

Thu Mar 11 08:09:19 2010

MMNL absent for 61854 secs; Foregrounds taking over
MMNL absent for 61854 secs; Foregrounds taking over
MMNL absent for 61854 secs; Foregrounds taking over

I'm just testing the dequeue, not doing anything with the data.

CREATE OR REPLACE PROCEDURE test_DEQUEUE_MSG IS

DETAINEE_TO_DEQUEUE DETAINEE_typ;

dopt dbms_aq.dequeue_options_t;

mprop dbms_aq.message_properties_t;

deq_msgid raw(16);

err_code number;

I NUMBER; qname varchar2(30);

no_messages exception;

pragma exception_init (no_messages, -25228);

new_detainees BOOLEAN := TRUE;

BEGIN dopt.dequeue_mode := dbms_aq.REMOVE;

dopt.navigation := dbms_aq.FIRST_MESSAGE;

dopt.consumer_name := 'INBOUND_DETAINEE';

dopt.wait := 1;

qname := 'INBOUND_DETAINEE_AQ';

I:=0;

WHILE (new_detainees) LOOP

begin

dbms_aq.dequeue(

queue_name => qname,

dequeue_options => dopt,

message_properties => mprop,

payload => DETAINEE_TO_DEQUEUE,

msgid => deq_msgid);

dbms_output.put_line (i||' DEQUEUED.');

I:=I+1;

commit;

exception

WHEN no_messages THEN

dbms_output.put_line (I||' DETAINEES DEQUEUED.');

dbms_output.put_line (' ---- NO MORE DETAINEES ---- ');

new_detainees := FALSE;

when others then

--show error code

err_code := SQLCODE;

dbms_output.put_line ('ERROR '||DETAINEE_TO_DEQUEUE.DTNEE_CIV_ID||' ORA-'||err_code);

END; end loop;

end;

/

 The output is:

0 DETAINEES DEQUEUED.
---- NO MORE DETAINEES ---- On Wed, Mar 10, 2010 at 9:15 PM, rjamya <rjamya_at_gmail.com> wrote:

> one question, how have you setup subscribers for remote queue (dequeue from
> a into b), and can you show the code for dequeue? sometimes you may have to
> prefix queue owner name to the queue. There are few things to look into.
>
> Raj
>
>

--
http://www.freelists.org/webpage/oracle-l