CODEWITHSUNDEEP
oracle-databaseplsqloracle11goracle12c
Kamil Zadora
Kamil Zadora

Reputation: 2397

ORA-04061: existing state of package body "PACKAGE.NAME" has been invalidated persists

On one of Oracle DB instances I am working on I am observing a different than normal behavior when recompiling packages.

Typically, (as in question Frequent error in Oracle ORA-04068: existing state of packages has been discarded) following error on first call is expected after PL/SQL package recompilation:

ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "PACKAGE.NAME" has been
invalidated
ORA-06508: PL/SQL: could not find program unit being called:
"PACKAGE.NAME"
ORA-06512: at line 1

But the second call should work fine, assuming the package has no errors of course. This behavior was present previously in that environment. In the meantime we upgraded from 11g R2 to 12c R1, and enabled edition based redefinition.

Now the problem I am experiencing is that I keep getting just:

ORA-04061: existing state of package body "PACKAGE.NAME" has been
invalidated
ORA-06508: PL/SQL: could not find program unit being called:
"PACKAGE.NAME"
ORA-06512: at line 1

So no ORA-04068 anymore, and only way to fix it is to reconnect the session or calling DBMS_SESSION.RESET_PACKAGE() manually (but I don't control all code that may be affected anyway), otherwise the problem persists on every call.

Are there any DB parameters that control this that could got tweaked? The problem is not specific to any particular PL/SQL package and it seems that it can be triggered by normal package invalidation when something it references, changes.

Thank you in advance.

Upvotes: 5

Views: 21178

Answers (3)

Oly
Oly

Reputation: 1

The problem is with timestamp. If you have a script where you first create the package and then try to call it, it is possible that the timestamp is the same (especially if the server is strong). I had the same error and solved it by entering dbms_lock.sleep (2) after creating the package.

Upvotes: 0

igortche
igortche

Reputation: 115

I got this error when I put into some procedure the DDL instructions like:

execute immediate ('drop sequence seq_table_1');
execute immediate ('create sequence seq_table_1 increment by 1 start with 1');

Even though I don't call this (private) procedure anywhere in the package I got this error by calling any other procedures, implemented after(!) this procedure in the package body. Putting the pragma serially_reusable; didn't help also. But when I moved the implementation of this mentioned procedure at the end of the package body, the error is disappeared.

Upvotes: 0

APC
APC

Reputation: 146229

Oracle does this because recompiling a PL/SQL package invalidates any session variables in use.

There isn't much we can do to avoid this, except by using good deployment practices. Don't deploy changes while the database is in use, make sure all the connections are properly disconnected, etc. Easier said than done in this age of CI/CD, zero downtime and other exciting innovations.

So there is one thing in the back of the locker: pragma serially_reusable;. This instruction means the package's state is maintained for the duration of a single server call. For instance if we have a PL/SQL block which calls an SR procedure three times any variables altered by that procedure will main the value across the three calls. But the next time we run the block - in the same session - the variables will have been reset to their starting values.

There are several limitations to serially reusable PL/SQL - for instance, it can't be used in SQL queries. But the big attraction from your perspective is no more ORA-04068 or ORA-04061 errors. No session state, nothing to invalidate.

pragma serially_reusable must be declared at the package level, and in the body as well as the spec. So you must be sure that none of the packaged procedures need to maintain state across server calls.

Upvotes: 6

Related Questions