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!

7 thoughts on “How to solve ORA-4068

  1. thomas kyte

    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. Laurent Schneider Post author

    SQL> exec P.X := 1

    PL/SQL procedure successfully completed.

    SQL> set serverout on
    SQL> exec dbms_output.put_line(p.x)

    PL/SQL procedure successfully completed.

    Indeed, thanks for the comment, the code is getting pretty useless then!

    I was actually reading 11.2.0.2 Improvements to Reduce the Occurrences of “Existing State of Packages Discarded” Errors
    , and by searching for ORA-4068 I thought I found a useful trick 😉

  3. joel garry

    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.

  4. Pingback: YAPO-4068: Yet Another Post on ORA-04068 « ORAganism

  5. connormcdonald

    Not sure I reckon its as black and white as Tom claims.

    Rightly or wrongly, developers often take their historical 3GL skills (C, C++, etc) and apply those principles to plsql.

    So where as C may have a header file, you often see a plsql package spec defined with “system wide constants”, which are then referenced by many other packages.

    When you change that package of globals (eg add a new one)…then ker splat 🙂

    So I think there *is* potentially a use for the pragma.

    Dont get me wrong – you could workaround the issue in other ways – the globals could be function calls just returning a constant etc…fine with greenfield project – not so fine with an code base that already littered with them.

Comments are closed.