Re: DBMS_AQ

roy wrote:
> dears.
>
> i have a procedure in my ora-db that permanently dequeus messages from
> an oracle-queue.
> like:
>
> dequeue_options.navigation := DBMS_AQ.first_message;
> LOOP
> DBMS_AQ.dequeue (queue_name =>
> 'PRESEL_REQ',
> dequeue_options =>
> dequeue_options,
> message_properties =>
> message_properties,
> payload => payload,
> msgid => msgid
> );
> dequeue_options.navigation := DBMS_AQ.next_message;
> END LOOP;
>
> what options du i have to handle this procedure - means how to start
> and how to stop?
> for the moment i start this procedure by calling it in an oracle job
> und stop it with kill -9 <spid>.
>
> is there a better way to do this?
>
> best regards,
>
> roy
>

My first question is why are you creating a job that deletes messages from the queue? Don't the subscribers need those messages? Why not have them delete the message once they have consumed the message?

But more specifically to your question, you have to kill this job because you have coded a loop with no exit condition. You need an EXIT WHEN statement somewhere inside the loop. You'll probably want this at the end, when the call to DBMS_AQ.next_message does not return a valid message. So you'll need some sort of conditional (IF-THEN) testing for the exit point, and then exit from the loop.

HTH,
Brian

-- 
===================================================================

Brian Peasland
oracle_dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown