oracle 您如何以编程方式识别存储过程的依赖项？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/297465/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do you programatically identify a stored procedure's dependencies?
提问by ninesided
Is it possible to write a PL/SQL query to identify a complete list of a stored procedures dependencies? I'm only interested in identifying other stored procedures and I'd prefer not to limit the depth of nesting that it gets too either. For example, if A calls B, which calls C, which calls D, I'd want B, C and D reported as dependencies for A.
是否可以编写 PL/SQL 查询来识别存储过程依赖项的完整列表？我只对识别其他存储过程感兴趣，我也不想限制嵌套的深度。例如，如果 A 调用 B，B 调用 C，C 调用 D，我希望 B、C 和 D 报告为 A 的依赖项。
回答by Eddie Awad
On this page, you will find the following query which uses the PUBLIC_DEPENDENCYdictionary table:
在此页面上，您将找到以下使用PUBLIC_DEPENDENCY字典表的查询：
SELECT lvl
, u.object_id
, u.object_type
, LPAD (' ', lvl) || object_name obj
FROM ( SELECT LEVEL lvl, object_id
FROM SYS.public_dependency s
START WITH s.object_id =
( SELECT object_id
FROM user_objects
WHERE object_name = UPPER ('&OBJECT_NAME')
AND object_type = UPPER ('&OBJECT_TYPE'))
CONNECT BY s.object_id = PRIOR referenced_object_id
GROUP BY LEVEL, object_id) tree
, user_objects u
WHERE tree.object_id = u.object_id
ORDER BY lvl
/
回答by darreljnz
I agree with EddieAwad.
我同意 EddieAwad。
Its valuable to point out that Oracle only tracks the dependencies down to the object level. If you have your stored procedures in a package you can only track the dependencies if the package, not the individual functions/procedures within the package.
值得指出的是，Oracle 只跟踪到对象级别的依赖关系。如果您将存储过程放在一个包中，则您只能跟踪包中的依赖关系，而不是包中的各个函数/过程。
If you're looking to track intra-package dependencies then you'll need a PLSQL parser.
如果您希望跟踪包内依赖项，那么您将需要一个 PLSQL 解析器。
回答by Dwayne King
Something else worth pointing out here, is that there are certain cases where the dependency may only be at runtime, which unfortunately will not show up in the metadata.
这里值得指出的另一件事是，在某些情况下，依赖项可能仅在运行时存在，不幸的是不会出现在元数据中。
For example, if you are constructing a SQL statement at runtime, you may have code similar to:
例如，如果您在运行时构造 SQL 语句，您可能有类似于以下内容的代码：
...
mysql := 'select count(*) from '||table_name_in;
execute immediate mysql;
...
I've been burnt by this a few times, but there's unfortunately no way to find these types of dependencies in advance since it potentially depends on user input.
我已经被这个问题烧了几次，但不幸的是，没有办法提前找到这些类型的依赖项，因为它可能取决于用户输入。
回答by Kalpesh Nayak
To get all details:
要获取所有详细信息：
select * from all_dependencies where owner = '&OWNER' and NAME='&OBJECT_NAME'
