ORA-04068 – What to do about it

One of the most irritating errors I’ve ever had to deal with is

        ORA-04068: existing state of packages has been discarded.

If you have ever received this error while incorporating connection pooling here is what you can do about it.

The error looks like this in SqlPlus.

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

This error can happen while using connection pooling and after the packages are either rebuilt or modified (such as is the case when promoting application changes during a maintenance window). In my case it was not common to restart web services after such a promotion. This means the web services still maintained open connections through the connection pooling feature of IIS. Leaving open connections while promoting pl/sql is what was causing our ORA-04068 errors.

To fix this problem all we had to do was add the following command immediately after our application code made a connection to the database.


The documented notes for this procedure are:

    — Deinstantiate all packages in this session. In other words, free
    — all package state. This is the situation at the beginning of
    — a session.

Since our application is under the impression we are creating a new database connection it is ok for us to perform a package reset. However, since we were using connection pooling a “brand new” database connection was not being established. Although our application was requesting a new connection it was receiving a pointer to a used connection that had already been established in the IIS connection pool. So … what we did was issue the dbms_session.reset_package which made the “used” database connection look like new again in respect to the package state.

Post a Comment