从包体中检索私有过程/函数的列表

2022-01-13 00:00:00 package oracle

我想从包体中获取包含所有私有过程/函数的列表.

I want to obtain a list with all private procedures/functions from a package body.

对于公共对象这很容易，但我不知道如何为私有对象做到这一点.

For public object it is easy but I have no idea how to do that for private objects.

推荐答案

私有函数的本质是它们是私有的.默认情况下没有公开它们的数据字典视图.USER_PROCEDURES 和 USER_ARGUMENTS 仅显示公共过程的信息(在包 spec0 中定义的那些.

The nature of private functions is that they are private. There are no data dictionary views which expose them by default. USER_PROCEDURES and USER_ARGUMENTS only show information for public procedures (the ones defined in a package spec0.

但是，我们可以使用 PL/SCOPE 获取有关它们的信息，但这样做需要一些额外的努力:

However, we can get information about them using PL/SCOPE, but doing so requires a little bit of additional effort:

  1. SQL>更改会话集 plscope_settings='IDENTIFIERS:ALL';
  2. SQL>alter package your_package 编译体；

现在您可以通过以下查询找到您的私人程序单元:

Now you can find your private program units with this query:

select ui.type, ui.name, ui.usage_id
from user_identifiers ui
where ui.object_name = 'YOUR_PACKAGE'
and ui.usage = 'DEFINITION'
and ui.type in ('PROCEDURE', 'FUNCTION')
minus
( select 'PROCEDURE', upr.procedure_name 
  from user_procedures upr
  where upr.object_name = 'YOUR_PACKAGE'
  union
  select 'FUNCTION', uarg.object_name
  from user_arguments uarg
  where uarg.package_name = 'YOUR_PACKAGE'
  and uarg.position = 0 
);

要获取私有过程的参数，请将上一个查询中的 USAGE_ID 插入此查询:

To get the arguments of a private procedure plug the USAGE_ID from the previous query into this query:

select ui.name
       , ui.type
       , ui.usage_id
       , ui2.type as param_datatype
from user_identifiers ui
     left join user_identifiers ui2
        on ui2.usage_context_id = ui.usage_id 
where ui.object_name = 'YOUR_PACKAGE'
and ui.usage = 'DECLARATION'
and ui.usage_context_id = :private_proc_usage_id
/

这需要是左连接，因为 user_identifiers 具有标量数据类型(字符、数字、日期、clob)但没有复杂数据类型(xmltype、用户定义类型)的数据类型条目.

This needs to be a left join because user_identifiers has datatype entries for scalar datatypes (character, number, date, clob) but not complex datatypes (xmltype, user-defined types).

我们可以从 PL/SCOPE 中获得大量有关过程的信息，尽管它不像查询 USER_PROCEDURES 或 USER_ARGUMENTS 那样简单(事实上，它非常笨拙).了解更多.请注意，PL/SCOPE 数据存储在 SYSAUX 表空间中，因此不要与 DBA 陷入困境！

We can get lots of information about procedures from PL/SCOPE, even though it is not as easy as querying USER_PROCEDURES or USER_ARGUMENTS (in fact, it is surprisingly clunky). Find out more. Be aware that PL/SCOPE data is stored on the SYSAUX tablespace, so don't get into hot water with your DBA!

相关文章