PL/SQL Package invalidated

 

I have a script that makes use of a package (PKG_MY_PACKAGE). I will change some of the fields in a query in that package and then recompile it (I don't change or compile any other packages). I run the script and I get an error that looks like 

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

I run the script again (without changing anything else in the system) and the script executes successfully.

I thought that when I compiled before I executed the script that would fix any invalid references. This is 100% reproducible, and the more I use this script the more annoying it gets. What could cause this, and what would fix it?

(oracle 10g, using PL/SQL Developer 7)

Background

existing state of packages has been discarded means, that your Package had some sort of state.

This is caused by a global variable stored in your Package Body.
Until 11.2.0.2, constants did also cause this behavior (see documentation).

Since the package has already been used in your session, Oracle assumes that this state is relevant for you. Some of these variables might have different values now, and when you recompile the Body, the values are reset.

This exception is thrown, so that your clients know that they can't rely on those variables any more.

Solutions

  • Remove all global variables and constants (before 11gR2) from the Package Body if possible
  • Replace global variables by DETERMINISTIC functions (as suggested by this answer)
  • Defining packages with PRAGMA SERIALLY_REUSABLE causes Oracle to re-initialize the global variables with every call to the server.
  • Close your session and reconnect before calling the package again.
  • Reset the state manually (see Paul James' answer)

    • If you're running stuff in a script try these commands in there prior to running the re-compiled code.. 

    exec DBMS_SESSION.RESET_PACKAGE
exec DBMS_SESSION.MODIFY_PACKAGE_STATE( DBMS_SESSION.REINITIALIZE )

    They do what the name might suggest.

    Possible issues you can have is:

  • The package/procedure you are calling is invalid (though it can work if called independently) check this query whether you have an entry of your package or objects used in your package in this all_objects view

    select * from all_objects where status = 'INVALID' and owner = 'SCHEMA_NAME';

  • Check your package is having global variables? if yes then check if those variable is not being changed by any other session preferable remove those global variables/use function

  • run below script to compile all the objects in your schema

    begin dbms_utility.compile_schema('SCHEMA_NAME',false); end;

  • Last option if none of the above works then remove all the procedures/function from your package, add new function and try to run your function from the trigger. check if this works then your package is in special lock. After adding a new function/proc it's state will be valid again and then you can add all your actual funcs/procs and remove the newly added function/proc.
    • 链接地址: http://www.djcxy.com/p/36924.html

    上一篇: Oracle错误ORA

    下一篇: PL / SQL包失效