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   quot ORA-06508  PL SQL  could not find program unit being called quot    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

User · Answer

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

User · Answer

seems like opening a new session is the key   see this answer   and here is an awesome explanation about this error

User · Answer

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

User · Answer

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

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

Examples related to oracle

Examples related to plsql

Examples related to anonymous