您如何以编程方式识别存储过程的依赖关系? [英] How do you programatically identify a stored procedure's dependencies?

问题描述

是否可以编写PL/SQL查询来标识存储过程依赖项的完整列表?我只对标识其他存储过程感兴趣，并且我也不希望限制嵌套的深度，因为嵌套的深度也不会太大.例如，如果A呼叫B，而C呼叫D，则我希望B，C和D被报告为对A的依赖.

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.

推荐答案

在此页面，您将找到以下查询，该查询使用 PUBLIC_DEPENDENCY 字典表:

On this page, you will find the following query which uses the PUBLIC_DEPENDENCY dictionary table:

 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
/

这篇关于您如何以编程方式识别存储过程的依赖关系?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！