有什么方法可以确定软件包在Oracle中是否具有状态? [英] Is there any way to determine if a package has state in Oracle?

问题描述

Oracle中是否有任何方法可以确定程序包是有状态的还是无状态的?我不知道数据字典中包含该信息的任何视图.

Is there any way in Oracle to determine whether a package has state or is stateless? I'm not aware of any view in the data dictionary that contains that information.

"ORA-04068:程序包字符串的现有状态已被丢弃"错误非常令人讨厌.可以通过从包中删除包变量来消除它. 11g引入了一个特性，即具有所有编译时常量的变量的程序包将被视为无状态.

The "ORA-04068: existing state of packages string has been discarded" error is rather annoying. It can be eliminated by removing package variables from the package. 11g introduced the feature that a package with variables that are all compile-time constants will be treated as stateless.

我可以有两个会话，然后在一个会话中编译该程序包，然后在另一个会话中调用它，看看它是否引发异常，但这需要在该程序包中调用一个过程，这可能是不希望的.

I could have two sessions and compile the package in one and call it in the other and see if it throws an exception, but that requires calling a procedure in the package, which may not be desirable.

推荐答案

听起来您想要的是能够列出所有可能具有状态的软件包.

It sounds like what you want is to be able to list all packages that may potentially have state.

您要寻找的只是具有任何全局变量或常量的软件包.对于单个包装，通过检查非常简单.但是，要查看模式中的所有软件包，可以使用PL/Scope:

What you're looking for is just packages that have any global variables or constants. For a single package, this is quite simple by inspection. To look across all packages in a schema, however, you could use PL/Scope:

首先，以架构所有者身份登录，在会话中打开PL/Scope:

First, log in as the schema owner, turn on PL/Scope in your session:

alter session set plscope_settings='IDENTIFIERS:ALL';

然后，重新编译所有程序包主体.

Then, recompile all your package bodies.

然后，运行此查询以查找在程序包级别声明的所有变量和常量:

Then, run this query to find all the variables and constants declared at the package level:

select object_name AS package,
       type,
       name AS variable_name
from user_identifiers
where object_type IN ('PACKAGE','PACKAGE BODY')
and usage = 'DECLARATION'
and type in ('VARIABLE','CONSTANT')
and usage_context_id in (
  select usage_id
  from user_identifiers
  where type = 'PACKAGE'
  );

我建议生成的软件包列表将成为您的目标.

I'd suggest the resulting list of packages will be your target.

如果您使用的是11gR2，则常量不再会导致此问题，因此您可以改用以下查询:

If you're on 11gR2, constants no longer cause this problem, so you'd use this query instead:

select object_name AS package,
       type,
       name AS variable_name
from user_identifiers
where object_type IN ('PACKAGE','PACKAGE BODY')
and usage = 'DECLARATION'
and type = 'VARIABLE'
and usage_context_id in (
  select usage_id
  from user_identifiers
  where type = 'PACKAGE'
  );

这篇关于有什么方法可以确定软件包在Oracle中是否具有状态?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！