21

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.

2240
  • 1,547
  • 2
  • 12
  • 30
battech
  • 803
  • 2
  • 13
  • 25
  • 2
    Are you also getting something like 'existing package state had been discarded'? If so running it a second time in the same session ought to work. But that would suggest your package has some state, i.e. a variable declared in the package rather than in a procedure (and nothing to do with the `dbms_output`). – Alex Poole Oct 15 '13 at 09:45

4 Answers4

23

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).

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • Totally agree with you. I could care into the imaginary numbers about that. Just run the damn proc. I've tried excepting it out from a calling anon block and it absolutely must run to completion before that magic flag will clear. Irritating! – Pecos Bill Mar 17 '15 at 00:29
  • Great explanation. I've had plenty of cases where the package is valid, explicitly compiled, calls attempted multiple times, but still get this error. For some reason the connection needs to be disconnected and reconnected before it will see the valid package. This scenario could be handled gracefully in a single call attempt without generating an error. An incredibly negative impact to any deployments occurring in large enterprise environments – AaronLS Jul 13 '20 at 22:00
7

seems like opening a new session is the key.

see this answer.

and here is an awesome explanation about this error

Community
  • 1
  • 1
mkb
  • 1,106
  • 1
  • 18
  • 21
4

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; /
Quanlong
  • 24,028
  • 16
  • 69
  • 79
friendmanish
  • 41
  • 1
4

I recompiled the package specification, even though the change was only in the package body. This resolved my issue

Brian Sebastian
  • 51
  • 5