当前位置：文江博客话题详情

提取有关包、过程和函数的元数据的本机方法？

发布于 2024-12-27 17:34:05 字数 368 浏览 2 评论 0原文

我希望能够向 Oracle 查询属于某个包的公共过程和函数的列表，大致如下：

select procedure_name from all_package_procedures where package_name = :my_package_name;

我也希望成为能够查询 Oracle 以获取给定过程或函数的参数列表，类似于：

selectparameter_name, in_or_out,parameter_type from all_function_parameters where function_name = :my_function_name;

这本身可能吗？如果没有，有人知道现有代码可以实现这一目标吗？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

去了角落 2025-01-03 17:34:05

您可以查询USER_OBJECTS & USER_PROCEDURES 获取所有过程的列表属于特定包的函数

SELECT procedure_name
FROM   user_procedures
WHERE  object_id = (SELECT object_id
                    FROM   user_objects
                    WHERE  object_name = '<YOUR-PACKAGE-NAME>'
                           AND object_type = 'PACKAGE')

替换 user_objects & user_procedures 与 all_objects & all_procedures 分别用于获取包和其他用户拥有的程序。

我还希望能够向 Oracle 查询给定过程或函数的参数列表，

为此，您可以查询 user_arguments 或 all_arguments 来获取对象的参数由当前用户拥有分别为所有用户

SELECT argument_name,
       data_type
FROM   user_arguments
WHERE  package_name = '<name-of-your-package-procedure-function>'

You can query USER_OBJECTS & USER_PROCEDURES to get a list of all procedures & functions belonging to a particular package

SELECT procedure_name
FROM   user_procedures
WHERE  object_id = (SELECT object_id
                    FROM   user_objects
                    WHERE  object_name = '<YOUR-PACKAGE-NAME>'
                           AND object_type = 'PACKAGE')

Replace user_objects & user_procedures with all_objects & all_procedures respectively to fetch packages & procedures owned by other users.

I also wish to be able to query Oracle for a list of parameters for a given procedure or function,

For this, you can query user_arguments or all_arguments to fetch parameters of on object owned by the current user & all users respectively

SELECT argument_name,
       data_type
FROM   user_arguments
WHERE  package_name = '<name-of-your-package-procedure-function>'

回复收藏 0 原文

把昨日还给我 2025-01-03 17:34:05

我自己的回答，源自萨迪亚斯，供其他人参考。下面是一个查询，用于提取给定包的所有过程及其参数的非规范化结果：

select     p.procedure_name
         , a.argument_name
         , a.data_type
         , a.defaulted
         , a.default_value
         , a.in_out
         , a.position
from       all_procedures p
inner join all_objects o
        on o.object_id = p.object_id
inner join all_arguments a
        on a.package_name = o.object_name
       and a.object_name = p.procedure_name
where      o.object_type = 'PACKAGE'
and        o.object_name = 'PACKAGE_NAME'
order by   p.procedure_name, a.position;

My own answer, derived from Sathyas, for the reference of others. Here is a single query to pull out a denormalized result of all procedures and their arguments for a given package:

select     p.procedure_name
         , a.argument_name
         , a.data_type
         , a.defaulted
         , a.default_value
         , a.in_out
         , a.position
from       all_procedures p
inner join all_objects o
        on o.object_id = p.object_id
inner join all_arguments a
        on a.package_name = o.object_name
       and a.object_name = p.procedure_name
where      o.object_type = 'PACKAGE'
and        o.object_name = 'PACKAGE_NAME'
order by   p.procedure_name, a.position;

回复收藏 0 原文

~没有更多了~