René Nyffenegger's collection of things on the web  
René Nyffenegger on Oracle  Most wanted  Feedback
 Follow @renenyffenegger

connect by nocycle [Oracle]  
Consider to following directed graph:
A>C<D>F>L   ^  ^ v v  v  B H>I J>K    v v v E G M
In order to store the graph, the following table is created:
create table directed_graph ( node_from char(1), node_to char(1) );
For each edge, a record is inserted:
insert into directed_graph values ('A', 'C'); insert into directed_graph values ('A', 'B'); insert into directed_graph values ('B', 'E'); insert into directed_graph values ('C', 'H'); insert into directed_graph values ('H', 'I'); insert into directed_graph values ('I', 'D'); insert into directed_graph values ('D', 'F'); insert into directed_graph values ('D', 'C'); insert into directed_graph values ('F', 'J'); insert into directed_graph values ('J', 'K'); insert into directed_graph values ('J', 'G'); insert into directed_graph values ('K', 'L'); insert into directed_graph values ('F', 'L'); insert into directed_graph values ('K', 'M');
Let's find all direct and inderect nodes pointed to from F:
select lpad(' ', level1)  node_from  '>'  node_to from directed_graph start with node_from = 'F' connect by prior node_to = node_from; F>J J>K K>L K>M J>G F>L
Similar query, but starting with A:
select lpad(' ', level1)  node_from  '>'  node_to from directed_graph start with node_from = 'A' connect by prior node_to = node_from;
Because there is a loop, Oracle says: ORA01436: CONNECT BY loop in user data.
The nocycle clause comes to rescue:
select lpad(' ', level1)  node_from  '>'  node_to from directed_graph start with node_from = 'A' connect by nocycle prior node_to = node_from;
It creates the following record set:
A>C C>H H>I I>D D>F F>J J>K K>L K>M J>G F>L A>B B>E
The query works now. However, the edge D>C is not displayed!
