ORA-4068 and CONSTANT keyword…good and bad

Anyone that has ever coded PLSQL will be familiar with the error ORA-4068, where you had some state persisted in a session due to a package variable, and then when you change the package, the state is cleared along with an ORA-4068.  Here’s a quick example:

 

Session 1 

SQL> create or replace
  2  package PKG is
  3    procedure P;
  4  end;
  5  /

Package created.

SQL> create or replace
  2  package body PKG is
  3      my_global int := 10;
  4    procedure P is
  5    begin
  6       null;
  7    end;
  8  end;
  9  /

Package body created.

Session 2 

SQL> exec pkg.p

PL/SQL procedure successfully completed.

Session 1

Now we change the package… In this case, I’ve just changed the value of the global

SQL> create or replace
  2  package body PKG is
  3      my_global int := 11;
  4    procedure P is
  5    begin
  6       null;
  7    end;
  8  end;
  9  /

Package body created.

Session 2 

And on next invocation, session 2 gets the error.  Not a great thing if you want to deploy changes to a live application (unless that application had the foresight to have appropriate handlers for ORA-4068).

SQL> exec pkg.p
BEGIN pkg.p; END;

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

 

One of the nice things to come in 11.2, was that if the global variable was declared as CONSTANT, then the compiler/runtime engines was intelligent enough to know that there was no state, and hence the ORA-4068 could be avoided.  For example, the code below will not suffer from the ORA-4068 error.

SQL> create or replace
  2  package body PKG is
  3      my_global CONSTANT int := 12;
  4    procedure P is
  5    begin
  6       null;
  7    end;
  8  end;
  9  /

Package body created.

This was cool because many a PLSQL programmer comes from a C, C++ or C# background, where constants are often used to enumerate static values, and so they use the same approach in PLSQL.  However, it appears that the compiler is not smart enough to deal with any form of expression.  For example, whilst the code below has a global defined as a constant…

SQL> create or replace
  2  package body PKG is
  3      my_global CONSTANT int := 13+15;
  4    procedure P is
  5    begin
  6       null;
  7    end;
  8  end;
  9  /

Package body created.

…because it is assigned via an expression, the ORA-4068 still rears it head Sad smile 

SQL> exec pkg.p
BEGIN pkg.p; END;

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

I discovered this when storing some constants for powers of 2 (for IP address manipulation).  So whilst

x := power(2,32)

conveys more meaning than

x := 4294967296

you need to be aware of the implications of doing so.

(plsql optimize level of 3 does not assist here).

Tested on 11.2.0.4 and 12.1.0.2

Advertisements

4 thoughts on “ORA-4068 and CONSTANT keyword…good and bad

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s