How to avoid the persistent state of package’s components?

Generally the persistent state of package’s component is useful, we don’t want to eliminate it.
Persistency means that the lifetime of package components(variables, cursors,exception definitions, etc.)
start with the instatiation of package and finish with the end of session.
Many developers collect, for example, the necessary exception definitions in a particular package and
later everybody can use them as many times in their session as they want without reloading them into memory.
Sometimes inconvenient, that our session “remembers” the previous state of package components.
Typical scenario when a package is instantiated in one session and later we modify the code of the package in an other session. After this modification in the first session we want to refer to an arbitrary component of the changed component we will get the following error message:

 
ORA-04068: existing state of packages has been discarded 
ORA-04061: existing state of package "HR.CS" has been invalidated 

Many times when we want to implement an algoritm with recursion, we need one or more “global variables”.
Let see an example:
Consider the algoritm for the classical “Tower of Hanoi”:
(http://www.mathsisfun.com/games/towerofhanoi.html)

 
CREATE OR REPLACE PROCEDURE hanoi(first pls_integer, 
second pls_integer, n pls_integer)
is
third pls_integer;
BEGIN
IF n=1 THEN
dbms_output.put_line(first||' => '||second);
RETURN;
END IF;
CASE 
WHEN  first * second=2 THEN third:=3;
WHEN  first * second=3 THEN third:=2;
WHEN  first * second=6 THEN third:=1;
END CASE;
hanoi(first, third,n-1);
hanoi (first,second,1);
hanoi(third,second,n-1);
END;
/

Whe want to run procedure for 3 level tower then program generates the necessary steps for moving the tower from the first (“1”) position to the second(“2”).
exec hanoi(1,2,3) — we want to move a 3 level tower from the first position to the second

 
PROCEDURE HANOI compiled
anonymous block completed
1 => 2
1 => 3
2 => 3
1 => 2
3 => 1
3 => 2
1 => 2

Let’s count the number of the steps.
Not surprisingly, the result : 7. (2**3 -1 , where the 3 is the height of the tower).
We want to see step number before printing step instruction.
Not very easy! We can not use a local variabe, we need a “global” variable.
The first attempt could be the following:
Let’s create a package , called hanoi_pkg!

 
CREATE OR REPLACE PACKAGE hanoi_pkg
is
procedure hanoi(first pls_integer, 
second pls_integer, n pls_integer);
END hanoi_pkg;
/
CREATE OR REPLACE PACKAGE BODY hanoi_pkg
IS
i pls_integer:=0;
PROCEDURE hanoi(first pls_integer, 
second pls_integer, n pls_integer)
is
third pls_integer;
BEGIN
IF n=1 THEN
i:=i+1;
dbms_output.put_line(i||'. step: '||first||' => '||second);
RETURN;
END IF;
CASE 
WHEN  first * second=2 THEN third:=3;
WHEN  first * second=3 THEN third:=2;
WHEN  first * second=6 THEN third:=1;
END CASE;
hanoi(first, third,n-1);
hanoi (first,second,1);
hanoi(third,second,n-1);
END;
END hanoi_pkg;
/

and run the procedure:
exec hanoi_pkg.hanoi(1,2,3)

 
anonymous block completed
1. step: 1 => 2
2. step: 1 => 3
3. step: 2 => 3
4. step: 1 => 2
5. step: 3 => 1
6. step: 3 => 2
7. step: 1 => 2

The result -seems to be- correct.
Please, run again:
exec hanoi_pkg.hanoi(1,2,3)

 
anonymous block completed
8. step: 1 => 2
9. step: 1 => 3
10. step: 2 => 3
11. step: 1 => 2
12. step: 3 => 1
13. step: 3 => 2
14. step: 1 => 2

The problem is that our session remembers the latest value of the counter!
The reason is that the components of the package are persistent.
The private variable “i” (see i pls_integer:=0;) is also persistent, even this is a private (not public) variable!
How to solve the problem?
Please use the SERIALLY_REUSABLE pragma ;
The modified package:

 
CREATE OR REPLACE PACKAGE hanoi_pkg
is
PRAGMA SERIALLY_REUSABLE ;
procedure hanoi(first pls_integer, 
second pls_integer, n pls_integer);
END hanoi_pkg;
/
CREATE OR REPLACE PACKAGE BODY hanoi_pkg
IS
i pls_integer:=0;
PRAGMA SERIALLY_REUSABLE ;
PROCEDURE hanoi(first pls_integer, 
second pls_integer, n pls_integer)
is
third pls_integer;
BEGIN
IF n=1 THEN
i:=i + 1;
dbms_output.put_line(i||'. step: '||first||' => '||second);
RETURN;
END IF;
CASE 
WHEN  first * second=2 THEN third:=3;
WHEN  first * second=3 THEN third:=2;
WHEN  first * second=6 THEN third:=1;
END CASE;
hanoi(first, third,n-1);
hanoi (first,second,1);
hanoi(third,second,n-1);
END;
END hanoi_pkg;
/

Now we test the modification:
exec hanoi_pkg.hanoi(1,2,3)

 
anonymous block completed
1. step: 1 => 2
2. step: 1 => 3
3. step: 2 => 3
4. step: 1 => 2
5. step: 3 => 1
6. step: 3 => 2
7. step: 1 => 2

Run again – when the package has been instantiated already!)
exec hanoi_pkg.hanoi(1,2,3)

 
anonymous block completed
1. step: 1 => 2
2. step: 1 => 3
3. step: 2 => 3
4. step: 1 => 2
5. step: 3 => 1
6. step: 3 => 2
7. step: 1 => 2

Finally we can state, that with
PRAGMA SERIALLY_REUSABLE ; statement we removed the instantiated package from the memory.
This can be very useful when we continously develop a package, while other sessions want to use it.

Thanks for visting my site!
My e-mail has changed:
laszlo.czinkoczki@webvalto.hu
but
czinkoczkilaszlo@gmail.com
is still alive.

Advertisements