dbms_session.reset_package

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. Received on Fri Feb 11 2005 - 09:22:57 CST