You've got a loop in your foreign keys somewhere! If it's a FK back to the same table you can avoid the problem by checking that the previous table <> to current with "prior table_name <> table_name":
create table t (
x int primary key,
y int references t(x)
);
select s.*,
rpad ( '*', ( level-1 ) *2, '*' ) || table_name table_name
from
(select p.table_name , p.constraint_name primary_cons ,
f.constraint_name foreign_cons ,
f.r_constraint_name foreign_key_name
from user_constraints p , user_constraints f
where p.table_name = f.table_name
and p.constraint_type = 'P'
and f.constraint_type = 'R'
union
select table_name , constraint_name , null ,
null
from user_constraints
where constraint_type = 'P'
) s
start with foreign_cons is null
connect by prior primary_cons = foreign_key_name;
SQL Error: ORA-01436: CONNECT BY loop in user data
select s.*,
rpad ( '*', ( level-1 ) *2, '*' ) || table_name table_name
from
(select p.table_name , p.constraint_name primary_cons ,
f.constraint_name foreign_cons ,
f.r_constraint_name foreign_key_name
from user_constraints p , user_constraints f
where p.table_name = f.table_name
and p.constraint_type = 'P'
and f.constraint_type = 'R'
union
select table_name , constraint_name , null ,
null
from user_constraints
where constraint_type = 'P'
) s
start with foreign_cons is null
connect by prior primary_cons = foreign_key_name
and prior table_name <> table_name;
TABLE_NAME PRIMARY_CONS FOREIGN_CONS FOREIGN_KEY_NAME TABLE_NAME
T SYS_C005783 T
If you have a larger loop in your relationships, you could cheat your way out of this with the "nocycle" keyword:
drop table t purge;
create table t (
x int primary key,
y int
);
create table t1 (
x int primary key,
y int references t(x)
);
alter table t add constraint fk foreign key (y) references t1(x);
select s.*,
rpad ( '*', ( level-1 ) *2, '*' ) || table_name table_name
from
(select p.table_name , p.constraint_name primary_cons ,
f.constraint_name foreign_cons ,
f.r_constraint_name foreign_key_name
from user_constraints p , user_constraints f
where p.table_name = f.table_name
and p.constraint_type = 'P'
and f.constraint_type = 'R'
union
select table_name , constraint_name , null ,
null
from user_constraints
where constraint_type = 'P'
) s
start with foreign_cons is null
connect by nocycle prior primary_cons = foreign_key_name
and prior table_name <> table_name;
TABLE_NAME PRIMARY_CONS FOREIGN_CONS FOREIGN_KEY_NAME TABLE_NAME
T SYS_C005797 T
T1 SYS_C005798 SYS_C005799 SYS_C005797 **T1
T1 SYS_C005798 T1
T SYS_C005797 FK SYS_C005798 **T