Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> dbms_session.reset_package

dbms_session.reset_package

From: stephen O'D <stephen.odonnell_at_gmail.com>
Date: 11 Feb 2005 07:22:57 -0800
Message-ID: <1108135377.074661.256440@g14g2000cwa.googlegroups.com>


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;
/

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US