ORA-06508: PL/SQL: could not find program unit being called

Question

I am using oracle 10g and toad 11.5. I am trying to call an api from an anonymous block.

If I recompile the api after adding dbms_output.put_line and then try to execute the anonymous block, it shows error as:

"ORA-06508: PL/SQL: could not find program unit being called".

However if I end current session and open a new session, then the anonymous block will execute with out the error.

Due to this issue, i am made to reconnect the session everytime i make a change to API. Can anyone help if this issue can be resolved by making any configurations in toad or database level.

Answer 1

I suspect you're only reporting the last error in a stack like this:

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

If so, that's because your package is stateful:

The values of the variables, constants, and cursors that a package declares (in either its specification or body) comprise its package state. If a PL/SQL package declares at least one variable, constant, or cursor, then the package is stateful; otherwise, it is stateless.

When you recompile the state is lost:

If the body of an instantiated, stateful package is recompiled (either explicitly, with the "ALTER PACKAGE Statement", or implicitly), the next invocation of a subprogram in the package causes Oracle Database to discard the existing package state and raise the exception ORA-04068.

After PL/SQL raises the exception, a reference to the package causes Oracle Database to re-instantiate the package, which re-initializes it...

You can't avoid this if your package has state. I think it's fairly rare to really need a package to be stateful though, so you should revisit anything you have declared in the package, but outside a function or procedure, to see if it's really needed at that level. Since you're on 10g though, that includes constants, not just variables and cursors.

But the last paragraph from the quoted documentation means that the next time you reference the package in the same session, you won't get the error and it will work as normal (until you recompile again).

Answer 2

seems like opening a new session is the key.

see this answer.

and here is an awesome explanation about this error

Answer 3

Based on previous answers. I resolved my issue by removing global variable at package level to procedure, since there was no impact in my case.

Original script was

create or replace PACKAGE BODY APPLICATION_VALIDATION AS 

V_ERROR_NAME varchar2(200) := '';

PROCEDURE  APP_ERROR_X47_VALIDATION (   PROCESS_ID IN VARCHAR2 ) AS BEGIN
     ------ rules for validation... END APP_ERROR_X47_VALIDATION ;

/* Some more code
*/

END APPLICATION_VALIDATION; /

Rewritten the same without global variable V_ERROR_NAME and moved to procedure under package level as

Modified Code

create or replace PACKAGE BODY APPLICATION_VALIDATION AS

PROCEDURE  APP_ERROR_X47_VALIDATION (   PROCESS_ID IN VARCHAR2 ) AS

**V_ERROR_NAME varchar2(200) := '';** 

BEGIN
     ------ rules for validation... END APP_ERROR_X47_VALIDATION ;

/* Some more code
*/

END APPLICATION_VALIDATION; /