the idea of this post was largely inspired by nuke_y on developpez.net (in French)
I will try to rephrase it in a hierarchical way.
Let’s take all managers of Adams in EMP :
SELECT empno, ename, mgr
FROM emp
CONNECT BY NOCYCLE PRIOR mgr = empno
START WITH ename = 'ADAMS';
EMPNO ENAME MGR
---------- ---------- ----------
7876 ADAMS 7788
7788 SCOTT 7566
7566 JONES 7839
7839 KING
So far so good. Let’s imagine SCOTT has two managers, JONES and PAUL.
create table lsc_t AS
(SELECT ename, empno, mgr
FROM emp
UNION ALL
SELECT 'SCOTT', 7788, 9999
FROM DUAL
UNION ALL
SELECT 'PAUL', 9999, NULL
FROM DUAL);
SELECT empno, ename, mgr
FROM lsc_t
CONNECT BY NOCYCLE PRIOR mgr = empno
START WITH ename = 'ADAMS';
EMPNO ENAME MGR
---------- ---------- ----------
7876 ADAMS 7788
7788 SCOTT 7566
7566 JONES 7839
7839 KING
7788 SCOTT 9999
9999 PAUL
EMP is not supposed to allow this (primary key on empno), so I created a table LSC_T.
So far still fine, Paul is in my list of managers.
Let’s imagine Scott is the manager of Paul. In 9i and below, this would result in an ORA-0146 CONNECT BY loop in user data, but in 10g, and according to the doc The NOCYCLE parameter instructs Oracle Database to return rows from a query even if a CONNECT BY loop exists in the data.
Should I try ???
UPDATE lsc_t
SET mgr = 7788
WHERE ename = 'PAUL';
COMMIT ;
SELECT empno, ename, mgr
FROM lsc_t
CONNECT BY NOCYCLE PRIOR mgr = empno
START WITH ename = 'ADAMS';
EMPNO ENAME MGR
---------- ---------- ----------
7876 ADAMS 7788
7788 SCOTT 7566
7566 JONES 7839
7839 KING
7788 SCOTT 9999
Due to a loop in user data, PAUL is no longer returned. I have no explanation so far.
NOCYCLE
!
) 
