Dependencies

W

What objects are used/referenced in selected object? You can explore user_dependencies view in latest documentation.

SELECT
    d.referenced_type,
    LPAD(' ', (LEVEL - 1) * 2) || d.referenced_name AS referenced_name
FROM user_dependencies d
WHERE d.referenced_owner    = USER
CONNECT BY NOCYCLE d.name   = PRIOR d.referenced_name
    AND d.type              = PRIOR d.referenced_type
START WITH d.name           = '&OBJECT_NAME'
    --AND d.type              = '&OBJECT_TYPE'
ORDER SIBLINGS BY d.referenced_name;

SELECT
    d.referenced_type,
    LISTAGG(d.referenced_name, ', ') WITHIN GROUP (ORDER BY d.referenced_name) AS referenced_names
FROM (
    SELECT DISTINCT d.referenced_type, d.referenced_name
    FROM user_dependencies d
    WHERE d.referenced_owner    = USER
    CONNECT BY NOCYCLE d.name   = PRIOR d.referenced_name
        AND d.type              = PRIOR d.referenced_type
    START WITH d.name           = '&OBJECT_NAME'
        --AND d.type              = '&OBJECT_TYPE'
) d
GROUP BY d.referenced_type
ORDER BY 1;

Who is using/calling selected object?

SELECT DISTINCT
    d.referenced_type,
    d.referenced_name
FROM user_dependencies d
WHERE d.referenced_owner        = USER
CONNECT BY NOCYCLE PRIOR d.name = d.referenced_name
    AND PRIOR d.type            = d.referenced_type
START WITH d.referenced_name    = '&OBJECT_NAME'
    --AND d.referenced_type       = '&OBJECT_TYPE'
ORDER BY 1, 2;

Source code

And if your code is compiled with identifiers, then you can explore depencencies in depth.

WITH z AS (
    SELECT
        i.line,
        i.col,
        i.usage,
        i.type,
        i.usage_id,
        i.usage_context_id,
        i.name,
        i.signature
    FROM user_identifiers i
    WHERE i.object_name     = '&OBJECT_NAME'
        --AND i.object_type   = '&OBJECT_TYPE'
)
SELECT
    LEVEL                                   AS depth,
    LPAD(' ', (LEVEL - 1) * 2) || z.name    AS path,
    z.usage,
    z.line,
    z.col,
    z.type,
    z.name
FROM z
CONNECT BY NOCYCLE z.usage_context_id = PRIOR z.usage_id
START WITH z.usage_context_id = 0
ORDER SIBLINGS BY z.line, z.col;