Re: dbms_session.reset_package

stephen O'D wrote:

> Guys,
>
> I have been bitten by a nasty Global variables problem in a batch
> processing application.
>
> Basically, the batch application lifts an order from a queue, processes
> it (using some packages that unfortuantely use global variables), then
> picks up another order. In some cases the Global variables are not
> getting reset causing serious problems:-
>
> procedure process_my_batch
> is
> begin
>
> for row in (select my next order) loop
> process_order
> end;
>
> end;
>
> What I hoped I could do to fix this is put a call to
> dbms_session.reset_package at the bottom of the loop, ie
>
> procedure process_my_batch
> is
> begin
>
> for row in (select my next order) loop
> process_order;
> dbms_session.reset_package;
> end;
>
> end;
>
> However this does not work as i hoped. Apparently the package state is
> only reset after the current PLSQL has finished. Does this mean after
> my processing has effectively exited? ie is the only way to use
> reset_pacakge like:-
>
> connect
>
> begin
> process_my_batch();
> end;
> /
>
> begin
> dbms_session.reset_package;
> end;
> /
>
> -- ... some time passes
>
> process_my_batch();
>
> ...
>
> disconnect
>
>
> Is there any way for me to reset the state of my packages between
> processing each order of my batch job within my loop?
>
> Thanks,
>
> Stephen.

Is the default state of the variable NULL? If so it may not reset. Why not just modify the global variable at the beginning/end of your code explicitly? And why use it at all if it is causing a problem?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)