René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

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(' ', level-1) || 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(' ', level-1) || 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: ORA-01436: CONNECT BY loop in user data.
The nocycle clause comes to rescue: 
select 
  lpad(' ', level-1) || 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!