Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 First, let me introduce you my old foe, ORA-04068 :

 Session 1:

 SQL> CREATE OR REPLACE PACKAGE P AS
   2  X NUMBER;Y NUMBER;END;
   3  / Package created.

SQL> exec P.X := 1

PL/SQL procedure successfully completed.

Session 2:

SQL> CREATE OR REPLACE PACKAGE P AS
   2  X NUMBER;Z NUMBER;END;
   3  /

Package created.

 Session 1:

SQL> exec P.X := 2
BEGIN P.X := 2;
END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "SCOTT.P" has been invalidated
ORA-04065: not executed, altered or dropped package "SCOTT.P"
ORA-06508: PL/SQL: could not find program unit being called: "SCOTT.P"
ORA-06512: at line 1

Changing the package in session 2 did invalidate the package variable in session 1.  As a result, the system threw an ORA-04068 error.

 And the PRAGMA that saves the world : PRAGMA SERIALLY_REUSABLE

This shows one way to avoid the ORA-04068 under certain circumstances.

Session 1:

 SQL> CREATE OR REPLACE PACKAGE P AS
   2  PRAGMA SERIALLY_REUSABLE;X NUMBER;Y NUMBER;END;
   3  /

Package created.

SQL> exec P.X := 1

PL/SQL procedure successfully completed.

 Session 2:

SQL> CREATE OR REPLACE PACKAGE P AS
   3  PRAGMA SERIALLY_REUSABLE;X NUMBER;Z NUMBER;END;
   2  /

Package created.

Session 1:

SQL> exec P.X := 2

PL/SQL procedure successfully completed.

Oh yes!  Note that the use of the pragma in this situation eliminated the ORA-04068 and the procedure completed successfully.

More information on ORA-04068 is available HERE.