CREATE OR REPLACE FORCE VIEW PLD.PLD_TABLE_HIERARCHY_VW
(
TABLE_NAME,
HIERARCHY_LEVEL
)
AS
WITH t
AS ( SELECT p.table_name table_name, LEVEL hierarchy_level
FROM all_constraints p
LEFT OUTER JOIN all_constraints r
ON r.r_constraint_name = p.constraint_name
AND r.owner = p.owner
WHERE p.owner = SYS_CONTEXT (‘USERENV’, ‘CURRENT_SCHEMA’)
AND ( (p.constraint_type = ‘P’ AND LEVEL = 1)
OR (p.constraint_type = ‘R’ AND LEVEL > 1))
CONNECT BY NOCYCLE PRIOR r.table_name = p.table_name)
SELECT table_name, MAX (hierarchy_level) hierarchy_level
FROM t
GROUP BY table_name;