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!
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).
SQL> exec P.X := 1PL/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 😉
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.
@Laurent Schneider
hi Laurent,
please note that this link is lying anyway
It states: “The new procedure DBMS_SESSION.RESET_SELF(), whose invocation must be in the package of interest, allows you to preempt the problem when it is safe to reinitialize the package state.”
However,
select * from dba_arguments where object_name like ‘RESET_SELF’
shows no row.
see also http://forums.oracle.com/forums/thread.jspa?threadID=2157054&tstart=61
@Sokrates
yes, I mentioned this to the document autor already 😉
Pingback: YAPO-4068: Yet Another Post on ORA-04068 « ORAganism
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.