Oracle dependency query

user_16793735 0
Viewed 37

To find first level dependency of objects dba_dependencies system table is used in Oracle, is there a way to find dependencies upto nth level based on the object.

For example, Object a -> depends on Object b -> depends on Object c

Desired output:

Object_name || Dependency_1 || Dependency_3
Object a    || Object b     || Object c

So the level has to be dynamic.

oracle sql
1 Answers

Maybe a few recursions could be usefull...

WITH
    obj AS
        (   Select &OwnerName "OWNED_BY", &ObjectName "OBJ_NAME", &ObjectType "OBJ_TYPE" From Dual  ), 
--  ---------------------------------------
    obj_deps_1 AS
        (   Select
                o.OBJ_NAME "OBJ_NAME",
                o.OBJ_TYPE "OBJ_TYPE",
                o.OWNED_BY "OBJ_OWNED_BY",
                --
                CASE WHEN d.REFERENCED_OWNER IN('SYS', 'PUBLIC') THEN Null ELSE d.REFERENCED_NAME END "REF_OBJ_NAME_1",
                CASE WHEN d.REFERENCED_OWNER IN('SYS', 'PUBLIC') THEN Null ELSE d.REFERENCED_TYPE END "REF_OBJ_TYPE_1",
                CASE WHEN d.REFERENCED_OWNER IN('SYS', 'PUBLIC') THEN Null ELSE d.REFERENCED_OWNER END "REF_OBJ_OWNED_BY_1"
                --
            From 
                obj o 
            Inner Join
                DBA_DEPENDENCIES d ON(d.OWNER = Upper(o.OWNED_BY) And d.NAME = Upper(o.OBJ_NAME) And d.TYPE = Upper(o.OBJ_TYPE))
        ),
    obj_deps_6 AS
        (
            SELECT DISTINCT
                o.OBJ_NAME "OBJ_NAME",
                o.OBJ_TYPE "OBJ_TYPE",
                o.OBJ_OWNED_BY "OBJ_OWNED_BY",
                --
                o.REF_OBJ_NAME_1 "REF_OBJ_NAME_1",
                o.REF_OBJ_TYPE_1 "REF_OBJ_TYPE_1",
                o.REF_OBJ_OWNED_BY_1 "REF_OBJ_OWNED_BY_1",
                --
                CASE WHEN d2.REFERENCED_OWNER IN('SYS', 'PUBLIC') THEN Null ELSE d2.REFERENCED_NAME END "REF_OBJ_NAME_2",
                CASE WHEN d2.REFERENCED_OWNER IN('SYS', 'PUBLIC') THEN Null ELSE d2.REFERENCED_TYPE END "REF_OBJ_TYPE_2",
                CASE WHEN d2.REFERENCED_OWNER IN('SYS', 'PUBLIC') THEN Null ELSE d2.REFERENCED_OWNER END "REF_OBJ_OWNED_BY_2",
                --
                CASE WHEN d3.REFERENCED_OWNER IN('SYS', 'PUBLIC') THEN Null ELSE d3.REFERENCED_NAME END "REF_OBJ_NAME_3",
                CASE WHEN d3.REFERENCED_OWNER IN('SYS', 'PUBLIC') THEN Null ELSE d3.REFERENCED_TYPE END "REF_OBJ_TYPE_3",
                CASE WHEN d3.REFERENCED_OWNER IN('SYS', 'PUBLIC') THEN Null ELSE d3.REFERENCED_OWNER END "REF_OBJ_OWNED_BY_3",
                --
                CASE WHEN d4.REFERENCED_OWNER IN('SYS', 'PUBLIC') THEN Null ELSE d4.REFERENCED_NAME END "REF_OBJ_NAME_4",
                CASE WHEN d4.REFERENCED_OWNER IN('SYS', 'PUBLIC') THEN Null ELSE d4.REFERENCED_TYPE END "REF_OBJ_TYPE_4",
                CASE WHEN d4.REFERENCED_OWNER IN('SYS', 'PUBLIC') THEN Null ELSE d4.REFERENCED_OWNER END "REF_OBJ_OWNED_BY_4",
                --
                CASE WHEN d5.REFERENCED_OWNER IN('SYS', 'PUBLIC') THEN Null ELSE d5.REFERENCED_NAME END "REF_OBJ_NAME_5",
                CASE WHEN d5.REFERENCED_OWNER IN('SYS', 'PUBLIC') THEN Null ELSE d5.REFERENCED_TYPE END "REF_OBJ_TYPE_5",
                CASE WHEN d5.REFERENCED_OWNER IN('SYS', 'PUBLIC') THEN Null ELSE d5.REFERENCED_OWNER END "REF_OBJ_OWNED_BY_5",
                --
                CASE WHEN d6.REFERENCED_OWNER IN('SYS', 'PUBLIC') THEN Null ELSE d6.REFERENCED_NAME END "REF_OBJ_NAME_6",
                CASE WHEN d6.REFERENCED_OWNER IN('SYS', 'PUBLIC') THEN Null ELSE d6.REFERENCED_TYPE END "REF_OBJ_TYPE_6",
                CASE WHEN d6.REFERENCED_OWNER IN('SYS', 'PUBLIC') THEN Null ELSE d6.REFERENCED_OWNER END "REF_OBJ_OWNED_BY_6"
            FROM
                obj_deps_1 o
            LEFT JOIN
                DBA_DEPENDENCIES d2 ON(d2.NAME = Nvl(o.REF_OBJ_NAME_1, 'no_object') And d2.TYPE = Nvl(o.REF_OBJ_TYPE_1, 'no_type'))
            LEFT JOIN
                DBA_DEPENDENCIES d3 ON(d3.NAME = Nvl(d2.REFERENCED_NAME, 'no_object') And d3.TYPE = Nvl(d2.REFERENCED_TYPE, 'no_type'))
            LEFT JOIN
                DBA_DEPENDENCIES d4 ON(d4.NAME = Nvl(d3.REFERENCED_NAME, 'no_object') And d4.TYPE = Nvl(d3.REFERENCED_TYPE, 'no_type'))
            LEFT JOIN
                DBA_DEPENDENCIES d5 ON(d5.NAME = Nvl(d4.REFERENCED_NAME, 'no_object') And d5.TYPE = Nvl(d4.REFERENCED_TYPE, 'no_type'))
            LEFT JOIN
                DBA_DEPENDENCIES d6 ON(d6.NAME = Nvl(d5.REFERENCED_NAME, 'no_object') And d6.TYPE = Nvl(d5.REFERENCED_TYPE, 'no_type'))
        )
SELECT 
    REPLACE('[' || OBJ_TYPE || '] "' || OBJ_NAME || '" (owner.' || OBJ_OWNED_BY || ')', '[] "" (owner.)', 'None') "OBJECT",
    REPLACE('[' || REF_OBJ_TYPE_1 || '] "' || REF_OBJ_NAME_1 || '" (owner.' || REF_OBJ_OWNED_BY_1 || ')', '[] "" (owner.)', 'None') "DEPENDENCY_1",
    REPLACE('[' || REF_OBJ_TYPE_2 || '] "' || REF_OBJ_NAME_2 || '" (owner.' || REF_OBJ_OWNED_BY_2 || ')', '[] "" (owner.)', 'None') "DEPENDENCY_2",
    REPLACE('[' || REF_OBJ_TYPE_3 || '] "' || REF_OBJ_NAME_3 || '" (owner.' || REF_OBJ_OWNED_BY_3 || ')', '[] "" (owner.)', 'None') "DEPENDENCY_3",
    REPLACE('[' || REF_OBJ_TYPE_4 || '] "' || REF_OBJ_NAME_4 || '" (owner.' || REF_OBJ_OWNED_BY_4 || ')', '[] "" (owner.)', 'None') "DEPENDENCY_4",
    REPLACE('[' || REF_OBJ_TYPE_5 || '] "' || REF_OBJ_NAME_5 || '" (owner.' || REF_OBJ_OWNED_BY_5 || ')', '[] "" (owner.)', 'None') "DEPENDENCY_5",
    REPLACE('[' || REF_OBJ_TYPE_6 || '] "' || REF_OBJ_NAME_6 || '" (owner.' || REF_OBJ_OWNED_BY_6 || ')', '[] "" (owner.)', 'None') "DEPENDENCY_6"
FROM
    obj_deps_6
ORDER BY
    OBJ_NAME, OBJ_TYPE,
    REF_OBJ_NAME_1, REF_OBJ_TYPE_1,
    REF_OBJ_NAME_2, REF_OBJ_TYPE_2,
    REF_OBJ_NAME_3, REF_OBJ_TYPE_3,
    REF_OBJ_NAME_4, REF_OBJ_TYPE_4,
    REF_OBJ_NAME_5, REF_OBJ_TYPE_5

Parameters should be entered inside single quotes (or you could put them inside the code). The resulting columns are formated like this:
[object_type] "object_name" (owner.owner_name)

user_190233530
Related