Sometime back, I wrote about the new 11gR2 feature that allows PLSQL to be changed without an outage. In that series of articles I pointed to 'fine grained dependency checking' that was introduced in 11gR1 as an enabler for this feature citing that online changes in 10g were impossible. However, there was a subtle, but now pretty obvious point that I missed back then, and some online changes are in fact possible in 10g

Not All Invalidations Are Equal

In Oracle 10g and before, if a package referenced a table, and the table was modified (by adding a column for example), the package would immediately be marked as invalid. In 11g this invalidation can be avoided, but even if the package does become invalid, it is not as bad as it seems.

The next time the package is called, execution will be delayed for a short period while Oracle recompiles it, and if the compile is successful, it will be called as normal. Even if a connected session has a prepared call to that procedure open, it will handle this sort of invalidation seamlessly. For example:

create table t1 (c1 integer);

create or replace package pkg1
as
  procedure p1;
end;
/

create or replace package body pkg1
as
  procedure p1 
  is
  begin
    for row in (select * from t1) loop
      null;
    end loop;
  end;
end;
/

Next I create a simple JDBC program using JRuby. This program doesn't do anything useful, but it illustrates the point. I first prepare and execute a call to my package, then I sleep. While the program is sleeping I add a column to T1 which will invalidate the package (because there is a select * in the procedure P1).

require 'java'
java_import 'oracle.jdbc.OracleDriver'
java_import 'java.sql.DriverManager'

oradriver = OracleDriver.new

DriverManager.registerDriver oradriver
@connection = DriverManager.get_connection
    "jdbc:oracle:thin:@localhost:1521/local11gr2.world",      
    'sodonnel', 'sodonnel'
@connection.auto_commit = false

call = @connection.prepare_call("begin pkg1.p1; end;")

call.execute
puts "called once"

sleep(15)

call.execute
puts "called twice"

While the JRuby program is sleeping, I executed the following code:

alter table t1 add (c2 integer);

select object_name, status 
from user_objects
where object_name like 'PKG1';

OBJECT_NAME         STATUS  
------------------- ------- 
PKG1                VALID   
PKG1                INVALID   

So the change to the table invalidated my package, but the JRuby program completed the second call to the package without an error, proving it is not an issue.

ORA-04068: existing state of packages has been discarded

In the above example, the invalidation of the package code is not a problem, as any application will recover seamlessly from it. However, a simple change to the package body can spoil the party:

create or replace package body pkg1
as
  g_var integer; -- <<- Global variable

  procedure p1 
  is
  begin
    for row in (select * from t1) loop
      null;
    end loop;
  end;
end;
/

By adding a global variable to the package, it now has internal state, and this is a problem. Even if this variable is a constant it will still cause problems on invalidations.

If you run the above example again, modifying the table while the JRuby code is sleeping, the second call will produce the following exception:

NativeException: java.sql.SQLException: ORA-04068: existing state of packages has been discarded

This is an artefact of how Oracle maintains the internal package state. A package with no global variable has no internal state, and when it is recompiled Oracle knows it does not have to throw an ORA-04068 if the package is accessed again in the same session.

However, a package with internal state (caused by having a package global variable) will lose that state when it is recompiled, causing the ORA-04068.

Avoiding ORA-04068

To avoid this error, all you have to do is avoid using global variables in your package. That can be easier said than done in some cases, and many well written applications will define a series of constants in the package unknowingly causing this problem.

A workaround is to define a 'constants package' that does nothing other that define the constants, and a separate package that accesses the tables and retrieves the constants from the constants package. In that way, the package with the internal state will not be invalidated by any changes to the underlying schema and active sessions will not see any errors.

In conclusion if you need to modify tables while the application is online, especially in 10g, give careful thought to how the code is organised to avoid unexpected ORA-04068 errors in the application.

Update - if you use stand alone functions or procedures in your application, you may want to read about how you can still get an ORA-04068, which was something I wasn't expecting.