oracle PL/SQL 包失效
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2502722/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PL/SQL Package invalidated
提问by FrustratedWithFormsDesigner
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
我有一个使用包 (PKG_MY_PACKAGE) 的脚本。我将更改该包中查询中的某些字段，然后重新编译它（我不更改或编译任何其他包）。我运行脚本并收到一个错误，看起来像
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?
我认为当我在执行脚本之前进行编译时，可以修复任何无效的引用。这是 100% 可重现的，我使用这个脚本越多，它就越烦人。什么可能导致这种情况，什么会解决它？
(oracle 10g, using PL/SQL Developer 7)
（oracle 10g，使用 PL/SQL Developer 7）
采纳答案by Peter Lang
Background
背景
existing state of packages has been discardedmeans, that your Package had some sort of state.
existing state of packages has been discarded意味着，您的包裹具有某种状态。
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).
这是由存储在包主体中的全局变量引起的。
在 11.2.0.2 之前，常量也确实会导致这种行为（请参阅文档）。
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.
由于该包已在您的会话中使用，因此 Oracle 假定此状态与您相关。其中一些变量现在可能具有不同的值，当您重新编译 Body 时，这些值将被重置。
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
DETERMINISTICfunctions (as suggested by this answer) - Defining packages with
PRAGMA SERIALLY_REUSABLEcauses 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)
- 如果可能，从包体中删除所有全局变量和常量（11gR2 之前）
- 用
DETERMINISTIC函数替换全局变量（如this answer所建议的那样） - 定义包
PRAGMA SERIALLY_REUSABLE会导致 Oracle 在每次调用服务器时重新初始化全局变量。 - 在再次调用包之前关闭会话并重新连接。
- 手动重置状态（参见Paul James 的回答）
回答by PaulJ
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.
他们按照名字可能暗示的那样做。
回答by Ankur Bhutani
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_objectsview
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.
您正在调用的包/过程无效（尽管如果独立调用它可以工作）检查此查询是否在此all_objects视图中包含包或包中使用的对象的条目
select * from all_objects where status = 'INVALID' and owner = 'SCHEMA_NAME';
检查你的包是否有全局变量？如果是，则检查这些变量是否没有被任何其他会话更改，最好删除那些全局变量/使用函数
运行以下脚本以编译架构中的所有对象
开始 dbms_utility.compile_schema('SCHEMA_NAME',false); 结尾;
- 最后一个选项，如果上述方法均无效，则从包中删除所有过程/函数，添加新函数并尝试从触发器运行您的函数。检查这是否有效，然后您的包裹处于特殊锁定状态。添加新函数/过程后，它的状态将再次有效，然后您可以添加所有实际的函数/过程并删除新添加的函数/过程。
回答by Paul
The above error : ORA-06508: PL/SQL: could not find program unit being called.
上述错误：ORA-06508：PL/SQL：找不到正在调用的程序单元。
is caused when an attempt was made to call a stored program that could not be found. The program may have been dropped or incompatibly modified, or have compiled with errors.
尝试调用无法找到的存储程序时导致。程序可能已被删除或不兼容地修改，或编译时出错。
Check that all referenced programs, including their package bodies, exist and are compatible.
检查所有引用的程序，包括它们的包体，是否存在并且兼容。
You can run this query to find invalid objects, which may cause the ORA-06508 error:
您可以运行此查询来查找无效对象，这可能会导致 ORA-06508 错误：
select comp_id, comp_name, version, status, namespace, schema from dba_registry;
从 dba_registry 中选择 comp_id、comp_name、版本、状态、命名空间、模式；
