The latest Oracle docs has the following design tip:
220.127.116.11 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.
You can use the *_dependency views and hierarchical queries (recursive with or connect by) to find loops.
Personally, I think recursive with works better for this as you define a CYCLE column with a flag to show if it's a loop or not:
create type teacher_t as object
create type class_t as object
teacher ref teacher_t
alter type teacher_t add attribute instructor_for ref class_t cascade
col name format a10
with dependency_loop (
) as (
select name, type
where name = 'TEACHER_T'
and referenced_owner = user
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