ORA-32044: cycle detected while executing recursive WITH query
Creating two tables and fill them with some test data.
create table tq84_elem (
id char (1) not null primary key,
data number(2) not null
);
create table tq84_link (
prev_elem not null references tq84_elem,
next_elem not null references tq84_elem,
primary key (prev_elem, next_elem)
);
begin
insert into tq84_elem values('A', 7 );
insert into tq84_elem values('B', 2 );
insert into tq84_elem values('C', 13 );
insert into tq84_elem values('D', 6 );
insert into tq84_elem values('E', 9 );
insert into tq84_link values('B', 'D');
insert into tq84_link values('B', 'A');
insert into tq84_link values('A', 'C');
insert into tq84_link values('C', 'D');
commit;
end;
/
Recursively querying the data:
with rec(prev_elem, next_elem, prev_data, next_data, lvl) as (
select
lnk.prev_elem,
lnk.next_elem,
elp.data,
eln.data,
0
from
tq84_link lnk join
tq84_elem elp on lnk.prev_elem = elp.id join
tq84_elem eln on lnk.next_elem = eln.id
where
prev_elem = 'B'
union all
select
lnk.prev_elem,
lnk.next_elem,
rec.next_data,
eln.data,
lvl + 1
from
rec join
tq84_link lnk on rec.next_elem = lnk.prev_elem join
tq84_elem eln on lnk.next_elem = eln.id
)
search depth first by prev_elem set ord
select
rpad(' ', lvl * 5) ||
prev_elem || ' (' || to_char(prev_data, 'fm99') || ') -> ' ||
next_elem || ' (' || to_char(next_data, 'fm99') || ')' x
from
rec
order by
ord;
The insert data essentially represents a directed graph with the values in tq84_elem being the nodes and the values in tq84_link the edges. The previous query's result is:
B (2) -> A (7)
A (7) -> C (13)
C (13) -> D (6)
B (2) -> D (6)
The current data does not have cycles in the directed graph, it thus is an directed acyclic graph.
Adding the following record creates a cycle in the graph (D -> A -> C -> D).
begin
insert into tq84_link values ('D', 'A');
commit;
end;
/
Because the data is now cyclic, the above recursive query causes the error ORA-32044: cycle detected while executing recursive WITH query with the previous query.
Using the cycle clause prevents the error:
with rec(prev_elem, next_elem, prev_data, next_data, lvl) as (
select
lnk.prev_elem,
lnk.next_elem,
elp.data,
eln.data,
0
from
tq84_link lnk join
tq84_elem elp on lnk.prev_elem = elp.id join
tq84_elem eln on lnk.next_elem = eln.id
where
prev_elem = 'B'
union all
select
lnk.prev_elem,
lnk.next_elem,
rec.next_data,
eln.data,
lvl + 1
from
rec join
tq84_link lnk on rec.next_elem = lnk.prev_elem join
tq84_elem eln on lnk.next_elem = eln.id
)
search depth first by prev_elem set ord
cycle prev_elem set cyl to 1 default 0 -- <<=== Use cycle clause to prevent error
select
rpad(' ', lvl * 5) ||
prev_elem || ' (' || to_char(prev_data, 'fm99') || ') -> ' ||
next_elem || ' (' || to_char(next_data, 'fm99') || ')' x
from
rec
order by
ord;
Preventing the error comes with the cost that the result set is now blown up because some links (such as D -> A) are reported multiple times, the result set is:
B (2) -> A (7)
A (7) -> C (13)
C (13) -> D (6)
D (6) -> A (7)
A (7) -> C (13)
B (2) -> D (6)
D (6) -> A (7)
A (7) -> C (13)
C (13) -> D (6)
D (6) -> A (7)