The latest Oracle docs has the following design tip:

9.13.5.2 Circular Dependencies Among Types
Avoid creating circular dependencies among types. In other words, do not create situations in which a method of type T returns a type T1, which has a method that returns a type T.

https://docs.oracle.com/en/database/oracle/oracle-database/21/adobj/design-consideration-tips-and-techniques.html

Attached is a link to LiveSQL that exhibits a very simple circular dependency that will likely have issues recompiling during a datapump. Assuming we already have a large application that the compiler is having issues with is there a query we can use to find instances where T1 references T2 and T2 references T1? We would also need to find them a few generations apart (T1 references T2, T2 references T3, T3 references T1). The reference may be either in an attribute (REF) or a subprogram (parameter or return type).

This would allow us to find what types may need to be changed to be brought in line with the latest documentation.

Thanks in advance for your help.

create type teacher_t as object  
    ( 
    id_num  integer, 
    first_name  varchar2(100) 
    ); 
/
create type class_t as object  
    ( 
    id_num  integer,  
    teacher ref teacher_t 
    ); 
/
alter type teacher_t add attribute instructor_for ref class_t cascade
/

col name format a10
with dependency_loop (
  name, type
) as (
  select name, type
  from   user_dependencies
  where  name = 'TEACHER_T'
  and    referenced_owner = user
  union  all
  select ud.name, ud.type
  from   user_dependencies ud
  join   dependency_loop dl
  on     ud.referenced_owner = user
  and    ud.referenced_name = dl.name 
  and    ud.referenced_type = dl.type
) cycle name set is_loop to 'Y' default 'N'
  select * from dependency_loop;
  
NAME       TYPE                I
---------- ------------------- -
TEACHER_T  TYPE                N
CLASS_T    TYPE                N
TEACHER_T  TYPE                Y