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.
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:
Post a Comment