Tag Archives: NOCYCLE

CONNECT BY NOCYCLE

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.