Skip to Main Content


Question and Answer

Connor McDonald

Thanks for the question, Jon.

Asked: May 12, 2022 - 7:49 pm UTC

Last updated: May 18, 2022 - 2:44 am UTC


Viewed 100+ times

You Asked

The latest Oracle docs has the following design tip: 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.

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.

with LiveSQL Test Case:

and Chris said...

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  
    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.type
  from   user_dependencies ud
  join   dependency_loop dl
  on     ud.referenced_owner = user
  and    ud.referenced_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


  (1 rating)


kate, May 18, 2022 - 2:28 am UTC

Connor McDonald
May 18, 2022 - 2:44 am UTC

glad we could help

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library