Error ORA-04068: existing state of packages has been discarded comes with package having variables defined in package specification or body. Whenever there are variables/constants defined in package specification or body that package is said to have a state. Upon first calling the package, the "state" is created in memory to hold the values of those variables etc.
This error is received when following changes are performed on package which has state :
- If an object that the package depends upon e.g. a table is altered in some way e.g. dropped and recreated, then because of the database dependencies, the package takes on an INVALID status.
- If there is some compilation error in package specification or body due to which it's state goes invalid.
- Value of any variable in the package specification or body is changed and package is compiled, in a different session.
- If we make any modification in package specification or body in a different session. Even if the change doesn't make the state INVALID.
If we compile such a package specification then all the procedures/packages referring procedure/function/variable of that procedure will give this error.
When you next make a call to the package, Oracle looks at the status and sees that it is invalid, then determines that the package has a "state". Because something has altered that the package depended upon, the state is taken as being out of date and is discarded, thus causing the "Package state has been discarded" error message.
NOTE: We will never receive this error if we do not have any variable/constant defined in a package.
Solution:
- Move all constants and variables into a stand-alone package specification and reference those from your initial package. Thus when the status of your original package is invalidated for whatever reason, it has no package state and can be recompiled automatically, however the package containing the variables/constants will not become invalidated as it has no dependencies, so the state that is in memory for that package will remain and can continue to be used.
- Make the package SERIALLY_REUSABLE. No state will be saved for the package and oracle will create new state of the package for every call
Simulation of ORA-04068: existing state of packages has been discarded error:
SQL> set serveroutput on
SQL> create table dependonme (x number)2 /
Table created.
SQL>
SQL> insert into dependonme values (5)
2 /
1 row created.
SQL>
SQL> create or replace package mypkg is
v_statevar number := 5;
procedure myproc;
end mypkg;
/
Package created.
SQL>
SQL> create or replace package body mypkg is
-- this means my package has a state
procedure myproc is
myval number;
begin
select x
into myval
from dependonme;
-- myval := myval * v_statevar;
DBMS_OUTPUT.PUT_LINE('My Result is: '||myval);
end;
end mypkg;
/
Package body created.
SQL>
SQL> exec mypkg.myproc
My Result is: 25
PL/SQL procedure successfully completed.
SQL> select object_name, object_type, status from user_objects where object_name = 'MYPKG';
OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------- -------
MYPKG PACKAGE VALID
MYPKG PACKAGE BODY VALID
1. Changing the Value of a variable declared in package specification:
I have a variable declared in package specification. This variable is not being used anywhere.
a). In same session:
SQL> create or replace package mypkg is
2 v_statevar number := 15;
3 procedure myproc;
4 end mypkg;
5 /
Package created.
SQL> exec mypkg.myproc;
My Result is: 5
Change of value in same session didn't make any error.
b). In a second session.
Chaning the value in session 2:
create or replace package mypkg is
v_statevar number := 25;
procedure myproc;
end mypkg;
/
Executing procedure in previos session 1:
SQL> exec mypkg.myproc;
BEGIN mypkg.myproc; END;
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "SCOTT.MYPKG" has been invalidated
ORA-04065: not executed, altered or dropped package "SCOTT.MYPKG"
ORA-06508: PL/SQL: could not find program unit being called: "SCOTT.MYPKG"
ORA-06512: at line 1
NOTE: Just compiling and not changing the value will not give this error. If value of variable is changed in session 2 only then we will receive this error.
2. Changing the Value of a variable declared in package body:
Similarly if the variable is declared in Package Body rather than in specification and if we make changes to that variable in package body in a different session, then again we will receive this error.
2
3 procedure myproc;
4 end mypkg;
5 /
Package created.
SQL> create or replace package body mypkg is
2 v_statevar number := 105;
3
4 procedure myproc is
5 myval number;
6 begin
7 select x
8 into myval
9 from dependonme;
10
11 myval := myval * v_statevar;
12 DBMS_OUTPUT.PUT_LINE('My Result is: '||myval);
13 end;
14 end mypkg;
15 /
Package body created.
SQL> exec mypkg.myproc;
My Result is: 525
PL/SQL procedure successfully completed.
SQL> create or replace package body mypkg is
2 v_statevar number := 205;
3
4 procedure myproc is
5 myval number;
6 begin
7 select x
8 into myval
9 from dependonme;
10
11 myval := myval * v_statevar;
12 DBMS_OUTPUT.PUT_LINE('My Result is: '||myval);
13 end;
14 end mypkg;
15 /
Package body created.
SQL> exec mypkg.myproc;
My Result is: 1025
PL/SQL procedure successfully completed.
Session 2: Compiling the package body with different value set for variable in it, in session 2 will generate this error.
SQL> create or replace package body mypkg is
2 v_statevar number := 305;
3
4 procedure myproc is
5 myval number;
6 begin
7 select x
8 into myval
9 from dependonme;
10
11 myval := myval * v_statevar;
12 DBMS_OUTPUT.PUT_LINE('My Result is: '||myval);
13 end;
14 end mypkg;
15 /
Package body created.
Session 1: Executing the dependent procedure again.
SQL> exec mypkg.myproc;
BEGIN mypkg.myproc; END;
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "SCOTT.MYPKG" has been invalidated
ORA-04065: not executed, altered or dropped package body "SCOTT.MYPKG"
ORA-06508: PL/SQL: could not find program unit being called: "SCOTT.MYPKG"
ORA-06512: at line 1
3. Making changes to object on which procedure in package body is dependend
a) Droping the table used in procedure body which maked the package body INVALID. These operation can be performed in same session.
SQL> select object_name, object_type, status from user_objects where object_name = 'MYPKG';
OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------- -------
MYPKG PACKAGE VALID
MYPKG PACKAGE BODY VALID
SQL> drop table dependonme;
Table dropped.
SQL> select object_name, object_type, status from user_objects where object_name = 'MYPKG';
OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------- -------
MYPKG PACKAGE VALID
MYPKG PACKAGE BODY INVALID
SQL> set serveroutput on
SQL> exec mypkg.myproc;
BEGIN mypkg.myproc; END;
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "SCOTT.MYPKG" has been invalidated
ORA-04065: not executed, altered or dropped package body "SCOTT.MYPKG"
ORA-06508: PL/SQL: could not find program unit being called: "SCOTT.MYPKG"
ORA-06512: at line 1
However if we did not have any variable defined then we would got the same error which is recieved on second call of this procedure.
SQL> exec mypkg.myproc;
BEGIN mypkg.myproc; END;
*
ERROR at line 1:
ORA-04063: package body "SCOTT.MYPKG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SCOTT.MYPKG"
ORA-06512: at line 1
NOTE:
Testing this same scenario but by declaring the variable in specificaiton rather then body would give the same result. So variable defined anywhere in package specification or body, when state of body get invalid, we will receieve this error.
4. Making changes to logic of procedure in package body
b). In Same session. No error will be generated if we change the logic in procedure.
SQL> create or replace package body mypkg is
2 procedure myproc is
3 myval number;
4 begin
5 select sal
6 into myval
7 from emp
8 where empno = 7369;
9
10 myval := myval * mypkg.v_statevar;
11 DBMS_OUTPUT.PUT_LINE('My Result is: '||myval);
12 end;
13 end mypkg;
14 /
Package body created.
SQL> set serveroutput on
SQL> exec mypkg.myproc;
My Result is: 80000
PL/SQL procedure successfully completed.
SQL> create or replace package body mypkg is
2 procedure myproc is
3 myval number;
4 begin
5 select x
6 into myval
7 from dependonme;
8
9 myval := myval * mypkg.v_statevar;
10 DBMS_OUTPUT.PUT_LINE('My Result is: '||myval);
11 end;
12 end mypkg;
13 /
Package body created.
SQL> set serveroutput on
SQL> exec mypkg.myproc;
My Result is: 500
PL/SQL procedure successfully completed.
b). Changing the package logic in a different session will generate this error.
Session 1:
-----------
SQL> exec mypkg.myproc;
My Result is: 500
PL/SQL procedure successfully completed.
Session 2:
-----------
SQL> create or replace package body mypkg is
2 procedure myproc is
3 myval number;
4 begin
5 select sal
6 into myval
7 from emp
8 where empno = 7369;
9
10 myval := myval * mypkg.v_statevar;
11 DBMS_OUTPUT.PUT_LINE('My Result is: '||myval);
12 end;
13 end mypkg;
14 /
Package body created.
Session 1:
-----------
SQL> exec mypkg.myproc;
BEGIN mypkg.myproc; END;
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "SCOTT.MYPKG" has been invalidated
ORA-04065: not executed, altered or dropped package body "SCOTT.MYPKG"
ORA-06508: PL/SQL: could not find program unit being called: "SCOTT.MYPKG"
ORA-06512: at line 1
SQL> set serveroutput on
SQL> exec mypkg.myproc;
My Result is: 80000
PL/SQL procedure successfully completed.
5. Checking the procedure specification variable change with two session
It has been observed that whenever there is a change in variable value in package specification, the package body got invalid and all the session who calls the procedure in this package starts showing "ORA-04068: existing state of packages has been discarded" error.
Session 1:
-----------
SQL> create or replace package mypkg is
2 v_statevar number := 40;
3 procedure myproc;
4 end mypkg;
5 /
Package created.
SQL> create or replace package body mypkg is
2 procedure myproc is
3 myval number;
4 begin
5 select x
6 into myval
7 from dependonme;
8
9 -- myval := myval * mypkg.v_statevar;
10 DBMS_OUTPUT.PUT_LINE('My Result is: '||myval);
11 end;
12 end mypkg;
13 /
Package body created.
SQL> select object_name, object_type, status from user_objects where object_name = 'MYPKG';
OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------- -------
MYPKG PACKAGE VALID
MYPKG PACKAGE BODY VALID
Session 2:
-----------
SQL> set serveroutput on
SQL> exec mypkg.myproc;
My Result is: 5
PL/SQL procedure successfully completed.
Session 3:
-----------
SQL> set serveroutput on
SQL> exec mypkg.myproc;
My Result is: 5
PL/SQL procedure successfully completed.
Session 1:
----------
SQL> create or replace package mypkg is
2 v_statevar number := 40;
3 procedure myproc;
4 end mypkg;
5 /
Package created.
SQL> select object_name, object_type, status from user_objects where object_name = 'MYPKG';
OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------- -------
MYPKG PACKAGE VALID
MYPKG PACKAGE BODY INVALID
Session 2
----------
SQL> set serveroutput on
SQL> exec mypkg.myproc;
BEGIN mypkg.myproc; END;
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "SCOTT.MYPKG" has been invalidated
ORA-04065: not executed, altered or dropped package "SCOTT.MYPKG"
ORA-06508: PL/SQL: could not find program unit being called: "SCOTT.MYPKG"
ORA-06512: at line 1
Session 3
----------
SQL> set serveroutput on
SQL> exec mypkg.myproc;
BEGIN mypkg.myproc; END;
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "SCOTT.MYPKG" has been invalidated
ORA-04065: not executed, altered or dropped package "SCOTT.MYPKG"
ORA-06508: PL/SQL: could not find program unit being called: "SCOTT.MYPKG"
ORA-06512: at line 1
6. Package Body Depended on Package Specification
This example shows package body is dependent on package specification and any change in it will make package body invalid. However we do not receive error while referring that package body procedure in same session because Package body is complied when we call the procedure.
SQL> create or replace package mypkg is
2 v_statevar number := 40;
3 end mypkg;
4 /
Package created.
SQL> create or replace package body mypkg is
2 procedure myproc is
3 myval number;
4 begin
5 select x
6 into myval
7 from dependonme;
8
9 -- myval := myval * mypkg.v_statevar;
10 DBMS_OUTPUT.PUT_LINE('My Result is: '||myval);
11 end;
12 end mypkg;
13 /
Package body created.
SQL> select object_name, status from user_objects where object_name='MYPKG';
OBJECT_NAME STATUS
---------- -------
MYPKG VALID MYPKG VALID
SQL> create or replace package mypkg is
2 v_statevar number := 140;
3 end mypkg;
4 /
Package created.
SQL> select object_name, status from user_objects where object_name='MYPKG';
OBJECT_NAME STATUS
-------------- -------
MYPKG VALID
MYPKG INVALID
SQL> create or replace package body mypkg is
2 procedure myproc is
3
4 begin
5 DBMS_OUTPUT.PUT_LINE('My Result is: ');
6 end;
7 end mypkg;
8 /
Package body created.
SQL> select object_name, status from user_objects where object_name='MYPKG';
OBJECT_NAME STATUS
----------------- -------
MYPKG VALID
MYPKG VALID
SQL> create or replace package mypkg is
2 v_statevar number := 240;
3 end mypkg;
4 /
Package created.
SQL> select object_name, status from user_objects where object_name='MYPKG';
OBJECT_NAME STATUS
--------------- -------
MYPKG VALID MYPKG INVALID
"ORA-04068: existing state of packages has been discarded" error is received only on a package which has variable defined in its package specification or body. We will get this error in following situaions:
- If value of variable defined in specification or body, is modified in a different session.
- Package specification or body got INVALID.
- Package specification or body is modified in a different session.
Solution:
With pragma SERIALLY_REUSABLE packageMaking the package SERIALLY_REUSABLE will avoid this error.
Session 1:
-----------
SQL> create or replace package mypkg is
2 pragma SERIALLY_REUSABLE;
3 v_statevar number := 5;
4 procedure myproc;
5 end mypkg;
6 /
Package created.
SQL> create or replace package body mypkg is
2 pragma SERIALLY_REUSABLE;
3 procedure myproc is
4 myval number;
5 begin
6 select x
7 into myval
8 from dependonme;
9
10 myval := myval * mypkg.v_statevar;
11 DBMS_OUTPUT.PUT_LINE('My Result is: '||myval);
12 end;
13 end mypkg;
14 /
Package body created.
SQL> exec mypkg.myproc;
My Result is: 25
PL/SQL procedure successfully completed.
Session 2: Modifying the variable value
SQL> create or replace package mypkg is
2 pragma SERIALLY_REUSABLE;
3 v_statevar number := 50;
4 procedure myproc;
5 end mypkg;
6 /
Package created.
Session 1:
SQL> exec mypkg.myproc;
My Result is: 250
PL/SQL procedure successfully completed.
0 comments:
Post a Comment