Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> ORA-04061

ORA-04061

From: <werner.fangmeier_at_esn-bochum.de>
Date: 16 Dec 2005 03:08:42 -0800
Message-ID: <1134731322.538933.145790@g47g2000cwa.googlegroups.com>


Hi all.

Many of us know the issue: You change a stateful package's body (i.e., having package variables) and recompile it; when using this package in the same session where it was instantiated, you will repeatedly receive the ORA-04061/ORA-04068 error chain ("existing state of packages has been discarded" resp. "existing state of package body "<Package Name>" has been invalidated"). Here's a quote from Metalink's Bug Description #2747350:

If a stateful package is instantiated in a particular session, and then

becomes invalidated, then all further references to that package in PL/SQL executed from SQL can result in an ORA-4061/ORA-4065 error message pair. Contrast this with the behavior if PL/SQL is invoked directly (not from SQL): a single set of ORA-4061/ORA-4065/ORA-4068 errors is given on the first reference; and subsequent references get no
errors.
Workaround:
  In the affected session, make a reference to the invalidated package   from PL/SQL outside of SQL. That reference will get the   ORA-4061/ORA-4063/ORA-4068; then subsequent references   (from either SQL or direct from PL/SQL) will run.

OK, so I tried the following; before the offending Statement calling the package and throwing the exception 4061, I inserted a block containing a "dummy" reference to the package, in order to throw the first exception, so that the following reference to the package will succeed like described above. To hide this from the customer, I tried to catch the exception:

select my_package.real_function(...) from ...
/

But, to my big surprise, this whole process only seems to work, if I do NOT catch the exception! That is, if I change the first block like this:

declare

   dummy varchar2(1);
begin

   dummy := my_package.dummy_function;
end;
/

it works in the sense, that this block throws the exception ORA-04061, which seems to make Oracle store the new version of the package in the SGA, so the following SELECT will succeed, since the package is validated again. But as soon as I insert the EXCEPTION handler into the "Dummy reference block", the error persists! But several sources (including Oracle themselves) do say, that the error from the first package reference CAN be caught in order to hide it from customers.

I also tried to insert more than one dummy reference into the block, but to no avail - when using exception handlers, it would not work, unless the block could throw an unhandled exception. Do I make a mistake, or does the exception handler prevent the re-validation process?

Any help is appreciated.

Werner Received on Fri Dec 16 2005 - 05:08:42 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US