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

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@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