SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
Let's consider this relation graph:
Jon -> Mike -> Paul -> Steve
^ |
| |
--------------Tom--------
We want to represent this into a table and retrieve the dependency chain:
SQL> create table hierarchy (col1 varchar2(15), col2 varchar2(15));
Table created.
SQL> insert into hierarchy values ('Jon','Mike');
1 row created.
SQL> insert into hierarchy values ('Mike','Paul');
1 row created.
SQL> insert into hierarchy values ('Paul','Steve');
1 row created.
SQL> insert into hierarchy values ('Steve','Tom');
1 row created.
SQL> insert into hierarchy values ('Tom','Jon');
1 row created.
SQL> commit;
Commit complete.
Now let's try to show this. But because 'Tom' loops back to 'Jon' an error will be raised:
SQL> select lpad(' ',level-1)|| col1 || '>>' || col2
2 from hierarchy
3 start with col1 = 'Jon'
4 connect by prior col1=col2;
ERROR:
ORA-01436: CONNECT BY loop in user data
no rows selected
The NOCYCLE keyword resolves this:
SQL> select lpad(' ',level-1)|| col1 || '>>' || col2
2 from hierarchy
3 start with col1 = 'Jon'
4 connect by NOCYCLE prior col1=col2;
LPAD('',LEVEL-1)||COL1||'>>'||COL2
-------------------------------------------------------
-------------------------------------------------------
-------------------------------------------------------
Jon>>Mike
Tom>>Jon
Steve>>Tom
Paul>>Steve
Mike>>Paul
Now we are trying to find who introduces the loop effect.
It seems that 'Mike' -> 'Paul' is causing it, yet I have no clue why is this reported like this.
SQL> select col1,col2,connect_by_iscycle, level
2 from hierarchy
3 start with col1 = 'Jon'
4 connect by NOCYCLE prior col1=col2;
COL1 COL2 CONNECT_BY_ISCYCLE LEVEL
--------------- --------------- ------------------ ----------
Jon Mike 0 1
Tom Jon 0 2
Steve Tom 0 3
Paul Steve 0 4
Mike Paul 1 5
