有没有办法确定一个包在 Oracle 中是否有状态?

2021-12-30 00:00:00 package state oracle11g 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'
  );

相关文章