Verificando recursivamente as constraints que levam a uma tabela

create or replace procedure recur_constraint(v_owner in varchar2,
v_table_name varchar2,
v_fk_constraint_name varchar2 default NULL,
v_r_constraint_name varchar2 default NULL,
v_r_status varchar2 default NULL,
level number default 0) is

i integer;
v_constraint_name dba_constraints.constraint_name%type;
v_status dba_constraints.status%type;

cursor c_constraint is
select owner, table_name, constraint_name, r_constraint_name, status
from dba_constraints
where dba_constraints.r_owner = v_owner
and dba_constraints.r_constraint_name = v_constraint_name
and dba_constraints.constraint_type = 'R';

begin
select constraint_name, status
into v_constraint_name, v_status
from dba_constraints
where owner = v_owner
and table_name = v_table_name
and constraint_type = 'P';

dbms_output.put_line('Level: ' || ' ' || level || ' ' || v_owner || ' ' ||
v_table_name || ' PK: ' || v_constraint_name || ' ' ||
v_status || ' FK: ' || v_fk_constraint_name ||
' -> ' || v_r_constraint_name || ' ' || v_r_status);

for c_rec in c_constraint loop
recur_constraint(c_rec.owner,
c_rec.table_name,
c_rec.constraint_name,
c_rec.r_constraint_name,
c_rec.status,
level + 1);

end loop;
exception
when no_data_found then
dbms_output.put_line('No record found for: ' || ' ' || v_owner || ' ' ||
v_table_name);
end;
/

-- Testando:

sql> set serveroutput on
sql> exec dbms_output.enable(1000000);
sql> exec recur_constraint(upper('&OWNER'),upper('&TABLE_NAME'));