OraFAQ Forum: SQL & PL/SQL » Initialisation of private variable

Home » SQL & PL/SQL » SQL & PL/SQL » Initialisation of private variable

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Initialisation of private variable [message #609715]

Tue, 11 March 2014 08:02

ibergo
Messages: 18
Registered: February 2014

Junior Member

Hi

One colleague challenged me with a problem: She was several runs of a procedure in a package, but ended up with different results. We soon detected that the problem was that see used a private package variable, that see forgot to initialize with each run. She asked me whether I knew any method for getting a private variable initialized from "outside". I rejected that it should be possible, but now I wonder if I was a bit to certain about that Smile

Smile

Here is a test example:

SQL> create or replace package foo_pck is
  2  procedure foo_count;
  3  end;
  4  /
Package created

SQL> create or replace package body foo_pck is
  2  v_count number := 0;
  3  procedure foo_count is
  4    begin
  5    	dbms_output.put_line('BEFORE v_count='||v_count);
  6      v_count := v_count + 1;
  7    	dbms_output.put_line('AFTER  v_count='||v_count);
  8    end;
  9  end;
 10  /
Package body created
SQL> begin
  2    for i in 1..3 loop
  3      dbms_output.put_line('Run '||i);
  4      foo_pck.foo_count;
  5    end loop;
  6  end;
  7  /
Run 1
BEFORE v_count=0
AFTER  v_count=1
Run 2
BEFORE v_count=1
AFTER  v_count=2
Run 3
BEFORE v_count=2
AFTER  v_count=3
PL/SQL procedure successfully completed

SQL>

The challenge is to find a method to have v_count get the values 0 and 1 in each of the three cases (without changing the package Smile

Smile

) , i.e. to have v_count initialized before each of the three calls to foo_pck.foo_count...

Report message to a moderator

Re: Initialisation of private variable [message #609718 is a reply to message #609715]

Tue, 11 March 2014 08:21

ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

There seems to be some ways to initialize the entire package (or to be exact *all* packages that are loaded at that point) again, mentioned on this page.

But that that also has other side effects, like losing the dbms_output lines for example.

Report message to a moderator

Re: Initialisation of private variable [message #609719 is a reply to message #609718]

Tue, 11 March 2014 08:27

ibergo
Messages: 18
Registered: February 2014

Junior Member

Hmmm.

I tried both dbms_session.reset_package; and dbms_session.modify_package_state(dbms_session.reinitialize);, but none of them have the desired effect in the context of a loop in a single anonymous block...

And the recompilation of the package causes a ddl lock. So that doesn't work neither.

[Updated on: Tue, 11 March 2014 08:47]

Report message to a moderator

Re: Initialisation of private variable [message #609722 is a reply to message #609719]

Tue, 11 March 2014 08:50

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

ibergo wrote on Tue, 11 March 2014 09:27

Hmmm.

I tried both dbms_session.reset_package; and dbms_session.modify_package_state(dbms_session.reinitialize);, but none of them have the desired effect in the context of a loop in a single anonymous block...

SCOTT@orcl > create or replace package foo_pck is
  2  procedure foo_count;
  3  end;
  4  /

Package created.

SCOTT@orcl > create or replace package body foo_pck is
  2  v_count number := 0;
  3  procedure foo_count is
  4    begin
  5  dbms_output.put_line('BEFORE v_count='||v_count);
  6      v_count := v_count + 1;
  7     dbms_output.put_line('AFTER  v_count='||v_count);
  8    end;
  9  end;
 10  /

Package body created.

SCOTT@orcl > exec dbms_session.reset_package

PL/SQL procedure successfully completed.

SCOTT@orcl > set serveroutput on
SCOTT@orcl > begin
  2      dbms_output.enable;
  3      foo_pck.foo_count;
  4  end;
  5  /
BEFORE v_count=0
AFTER  v_count=1

PL/SQL procedure successfully completed.

SCOTT@orcl > exec dbms_session.reset_package

PL/SQL procedure successfully completed.

SCOTT@orcl > set serveroutput on
SCOTT@orcl > begin
  2      dbms_output.enable;
  3      foo_pck.foo_count;
  4  end;
  5  /
BEFORE v_count=0
AFTER  v_count=1

PL/SQL procedure successfully completed.

SCOTT@orcl > exec dbms_session.reset_package

PL/SQL procedure successfully completed.

SCOTT@orcl > set serveroutput on
SCOTT@orcl > begin
  2      dbms_output.enable;
  3      foo_pck.foo_count;
  4  end;
  5  /
BEFORE v_count=0
AFTER  v_count=1

PL/SQL procedure successfully completed.

SCOTT@orcl >

However using dbms_session.reset_package isn't good solution - it resets ALL session packages.

SY.

Report message to a moderator

Re: Initialisation of private variable [message #609724 is a reply to message #609722]

Tue, 11 March 2014 09:03

ibergo
Messages: 18
Registered: February 2014

Junior Member

Yep. It works. But the point is that the initialisation/reset should be done within the loop. In the real world problem it is a loop that iterates thousands of times during one run.

Report message to a moderator

Re: Initialisation of private variable [message #609725 is a reply to message #609724]

Tue, 11 March 2014 09:13

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Well, you can do that inside the loop - no diff. However reset package will, as I already mentioned, reset package will reset all session packages including DBMS_OUTPUT, so you'll lose the output. So I added get_count function to the package just to show v_count value:

SCOTT@orcl > drop package foo_pck
  2  /

Package dropped.

SCOTT@orcl > create or replace package foo_pck is
  2  procedure foo_count;
  3  function get_count return number;
  4  end;
  5  /

Package created.

SCOTT@orcl > create or replace package body foo_pck is
  2  v_count number := 0;
  3  procedure foo_count is
  4    begin
  5  dbms_output.put_line('BEFORE v_count='||v_count);
  6      v_count := v_count + 1;
  7     dbms_output.put_line('AFTER  v_count='||v_count);
  8    end;
  9  function get_count return number
 10  is
 11  begin
 12      return v_count;
 13  end;
 14  end;
 15  /

Package body created.

SCOTT@orcl > set serveroutput on
SCOTT@orcl > begin
  2     for i in 1..3 loop
  3       dbms_output.put_line('Run '||i);
  4       foo_pck.foo_count;
  5       dbms_session.reset_package;
  6     end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SCOTT@orcl > set serveroutput on
SCOTT@orcl > exec dbms_output.put_line(foo_pck.get_count)
0

PL/SQL procedure successfully completed.

SCOTT@orcl >

SY.

Report message to a moderator

Re: Initialisation of private variable [message #609726 is a reply to message #609725]

Tue, 11 March 2014 09:24

ibergo
Messages: 18
Registered: February 2014

Junior Member

No. I still think you are "cheating" due to running two blocks. Watch this:

SQL> create table foo_tab (seq number,v_count number);
Table created

SQL> 
SQL> create or replace package foo_pck is
  2  procedure foo_count;
  3  end;
  4  /
Package created

SQL> 
SQL> create or replace package body foo_pck is
  2  v_count number := 0;
  3  v_seq   number := 0;
  4  procedure foo_count is
  5    begin
  6    	v_seq := v_seq + 1;
  7    	insert into foo_tab values (v_seq,v_count);
  8      v_count := v_count + 1;
  9    	v_seq := v_seq + 1;
 10    	insert into foo_tab values (v_seq,v_count);
 11    end;
 12  end;
 13  /
Package body created

SQL> 
SQL> begin
  2    for i in 1..3 loop
  3      foo_pck.foo_count;
  4      dbms_session.reset_package;
  5    end loop;
  6  end;
  7  /
PL/SQL procedure successfully completed

SQL> select * from foo_tab order by seq;
       SEQ    V_COUNT
---------- ----------
         1          0
         2          1
         3          1
         4          2
         5          2
         6          3
6 rows selected

SQL>

Report message to a moderator

Re: Initialisation of private variable [message #609729 is a reply to message #609726]

Tue, 11 March 2014 09:32

ibergo
Messages: 18
Registered: February 2014

Junior Member

...or even simpler:

SQL> create table foo_tab (v_count number);
Table created

SQL> 
SQL> create or replace package foo_pck is
  2  procedure foo_count;
  3  end;
  4  /
Package created

SQL> 
SQL> create or replace package body foo_pck is
  2  v_count number := 0;
  3  procedure foo_count is
  4    begin
  5    	insert into foo_tab values (v_count);
  6      v_count := v_count + 1;
  7    	insert into foo_tab values (v_count);
  8    end;
  9  end;
 10  /
Package body created

SQL> 
SQL> begin
  2    for i in 1..3 loop
  3      foo_pck.foo_count;
  4      dbms_session.reset_package;
  5    end loop;
  6  end;
  7  /
PL/SQL procedure successfully completed

SQL> select * from foo_tab;
   V_COUNT
----------
         0
         1
         1
         2
         2
         3
6 rows selected

SQL>

Report message to a moderator

Re: Initialisation of private variable [message #609747 is a reply to message #609729]

Tue, 11 March 2014 12:01

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Yes, you are right, it looks like reset doesn't affect currently running PL/SQL block. Not sure if it is a bug or correct behavior. You'd have to open a SR with Oracle.

SY.

Report message to a moderator

Re: Initialisation of private variable [message #609748 is a reply to message #609747]

Tue, 11 March 2014 13:49

ibergo
Messages: 18
Registered: February 2014

Junior Member

Yes. As mentioned earlier I have experimented with reset_package and modify_package_state. I have also tried with different uses of dynamic sql - hoped it might run in its own context. But so far, I havn't found a working solution.

But thank you for offering time for input.

Report message to a moderator

Re: Initialisation of private variable [message #609749 is a reply to message #609747]

Tue, 11 March 2014 13:52

ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Ah, it seems to be the documented behaviour.

Note:
RESET_PACKAGE only frees the memory, cursors, and package variables after the PL/SQL call that made the invocation finishes running.

Report message to a moderator

Re: Initialisation of private variable [message #609768 is a reply to message #609749]

Wed, 12 March 2014 02:35

ibergo
Messages: 18
Registered: February 2014

Junior Member

Good finding!

Which brings me back to the starting point: Are there any ideas/ways to get private package variables initialized during run of a single pl/sql block?

I still tend to think there isn't...

Report message to a moderator

Re: Initialisation of private variable [message #609775 is a reply to message #609768]

Wed, 12 March 2014 03:59

vslabs
Messages: 26
Registered: March 2014
Location: Cape Town

Junior Member

Correct. There is not.

It requires either a code change - which is what should be considered to do to buggy code.

Or it requires (in order to leave the base code unchanged), a wrapper that applies the fix on behalf of the base code. The wrapper can for example reset the base code's state (can be forced by doing an alter..compile of the base code, prior to calling it).

However, that is not how wrappers should be used. It is ideal for abstracting system calls for example. But a poor idea for a means to work around bugs.

Report message to a moderator

Re: Initialisation of private variable [message #609778 is a reply to message #609775]

Wed, 12 March 2014 04:19

ibergo
Messages: 18
Registered: February 2014

Junior Member

Agreed. A code change is of course the alternative and nicest way to solve the problem. The only reason why to put effort in this challenge is that I would like to avoid patching to the production system - or at least postpone it to a more appropriate time - if I can do a fix in the script instead.

Runtime compilation of the base code might be an approach theoretically but by no way in the real context Smile

Smile

Report message to a moderator

Re: Initialisation of private variable [message #609781 is a reply to message #609778]

Wed, 12 March 2014 04:31

ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Depending on how often the procedure needs to be called in what way it might even be an option to write the (OS) scripts in a way that they connect, run the procedure once, and then disconnect again.

Report message to a moderator

Re: Initialisation of private variable [message #609783 is a reply to message #609781]

Wed, 12 March 2014 04:45

ibergo
Messages: 18
Registered: February 2014

Junior Member

I consider recommending to test a similar solution: Let the original loop run, but instead of letting it start the problematic procedure, let it write text in terms of new anonymous pl/sql blocks (including calls to dbms_session.reset_package) to a new temporary script, and run that script afterwards.

I think that ought to work (havn't testet yet), but it of course might introduce performance issues. The question is just how big they will end up to be. The real world context is a batch program, that loops through "some" thousands of records, and runs for 5-6 hours once or twice a year. Next time is in the end of the month, which is why we don't have too much time to do a real code fix.

Report message to a moderator

Previous Topic:	How to mainatin history of table.
Next Topic:	Oracle SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 17:12:54 CDT 2024