select all oracle pl-sql objects dependents

Question

Is it possible to select all oracle pl-sql objects dependents from oracle system tables?
it must be look like data from ALL_DEPENDENCIES but for PL-SQL code (function and procedure)

Answer 1

You can not get that kind of information from only one system table but script like below maybe works for your needs. Try it.

SELECT A.OBJECT_ID,
       A.OBJECT_TYPE,
       A.OWNER,
       A.OBJECT_NAME,
       B.OWNER REF_OWNER,
       B.OBJECT_TYPE REF_TYPE,
       B.OBJECT_NAME REF_NAME,
       B.OBJECT_ID REF_ID,
       B.STATUS REF_STATUS
  FROM SYS.DBA_OBJECTS A,
       SYS.DBA_OBJECTS B,
       (    SELECT OBJECT_ID, REFERENCED_OBJECT_ID
              FROM (SELECT OBJECT_ID, REFERENCED_OBJECT_ID
                      FROM PUBLIC_DEPENDENCY
                     WHERE REFERENCED_OBJECT_ID <> OBJECT_ID) PD
        START WITH OBJECT_ID = ??
--                      (SELECT 
--                              OBJECT_ID
--                         FROM ALL_OBJECTS
--                        WHERE OBJECT_NAME = ??)
        CONNECT BY PRIOR REFERENCED_OBJECT_ID = OBJECT_ID) C
 WHERE     A.OBJECT_ID = C.OBJECT_ID
       AND B.OBJECT_ID = C.REFERENCED_OBJECT_ID
       AND A.OWNER NOT IN ('SYS', 'SYSTEM')
       AND B.OWNER NOT IN ('SYS', 'SYSTEM')
       AND A.OBJECT_NAME <> 'DUAL'
       AND B.OBJECT_NAME <> 'DUAL'
--       AND B.OBJECT_TYPE IN ('TABLE')