How to solve ORA-4068

I was amazed by this oneliner in stackoverflow.

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.

And the PRAGMA that saves the world : PRAGMA SERIALLY_REUSABLE

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!

6 thoughts on “How to solve ORA-4068”

  1. But that is totally defeating the purpose of global variables in a plsql package!

    The global variable is supposed to retain the value for the life of the session. If you use serially reusable – then the global variable lives only for the duration of the CALL (not the session, just one call to the database).

    If it only needs to live for the life of the call, then it should be a scoped variable in a procedure or function – and then you would not have the 4068 problem at all.

    The ora-4068 happens simply because you have global variables that are RESET to their default when you create or replace the package – and we have to tell you. With serially reusable they are reset every single call! defeating their purpose. If you can use serially reusable, then you didn’t need the global in the first place!!!

    In 11g Release 2 – the real way to “solve” the ora-4068 is to use edition based redefinition. You can put a new version of the package into production without affecting the old package. You would let existing sessions continue to use the old package (and hence their session state, their global variables, would not be affected). New sessions would start using the new package and have their state.

    Serially reusable is not a solution to this in my opinion. IF your code can use serially reusable – THEN it did not need globals in the first place (you are not maintaining a state).

  2. From http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/packages.htm#LNPLS99977 I wonder if there might be a semantics problem – I certainly can’t disagree with Tom’s assertion about global variables and sessions, but I think these are a different animal – variables that only exist for a call – a package can have many logical items within that call. So for those things, you wouldn’t want to add up lots of useless copies in many users UGA’s. Have a pool of them in the SGA, scalability win.

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>