从程序包主体检索私有过程/功能的列表 [英] Retreive a list of private procedures/functions from a package body

问题描述

我想从程序包主体中获取包含所有私有过程/功能的列表.

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> alter session set plscope_settings='IDENTIFIERS:ALL';
2. SQL> alter package your_package compile body;

1. SQL> alter session set plscope_settings='IDENTIFIERS:ALL';
2. SQL> alter package your_package compile body;

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

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).

即使不像查询USER_PROCEDURES或USER_ARGUMENTS一样容易，我们也可以从PL/SCOPE中获得许多有关过程的信息(实际上，这很笨拙). 了解更多.请注意，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!

这篇关于从程序包主体检索私有过程/功能的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！