05 July 2007

Some Notes on ORA-04061, ORA-04065 and ORA-06508

Today, i was doing unit tests of a PL/SQL package that is developed by me. As every oracle developer knows, if you compile a package and if it has some private or public variables in either package body or spec, they have to be reinstantiniated.(for more on package states, please refer to Dependent Objects and Object Statuses In Oracle). That is if you compile a package and then call a procedure in it, you can error. But when you call it again, the state will be refreshed and no more error would occur. But the situation i have faced today was a bit more different. I got the error, although i call the proc more than once:
ORA-04061: existing state of package body "HR.PKG" has been invalidated
ORA-04065: not executed, altered or dropped package body "HR.PKG""
ORA-06508: PL/SQL: could not find program unit being called

When i investigate the error, all objects are valid. Normally on second run, session state is cleaned(expected) but; in one of my runs, i get the same error again and again whereas running the same procedure. Somehow Oracle could not clean session state(unexpected). When i run the same proc in another session, in a new sql window, it works fine.
I could not understand why this happened. If I were using a connection-pooled mechanism(such as web servers), the session may not have been reinstantiniated. I have to restart restart the web server, that is clean connections.But i am using a simple desktop application to connect and execute procedures in oracle.

No comments: