How to implement CONNECT BY features

 ORACLE 11g release 2 introduced the recursive WITH clause as an alternative to the well known CONNECT BY clause.
This Blog entry shows how to implement all the CONNECT BY features using the new recursive WITH clause.

Root and Path

CONNECT BY clause knows the CONNECT_BY_ROOT operator which returns the root(s) of a hierarchy. Furthermore the SYS_CONNECT_BY_PATH function may be used to get a path from the root to the current element within the hierarchy:

SELECT e.ename, e.empno, e.mgr
      ,SYS_CONNECT_BY_PATH(e.ename,'/') AS path
      ,CONNECT_BY_ROOT e.ename          AS root
  FROM emp e
CONNECT BY PRIOR e.empno = e.mgr
 START WITH e.mgr IS NULL
 ORDER SIBLINGS BY e.ename
/

ENAME    EMPNO   MGR  PATH                     ROOT
-------  -----  ----  -----------------------  ----
KING      7839        /KING KING               
BLAKE     7698  7839  /KING/BLAKE              KING
ALLEN     7499  7698  /KING/BLAKE/ALLEN        KING
JAMES     7900  7698  /KING/BLAKE/JAMES        KING
MARTIN    7654  7698  /KING/BLAKE/MARTIN       KING
TURNER    7844  7698  /KING/BLAKE/TURNER       KING
WARD      7521  7698  /KING/BLAKE/WARD         KING
CLARK     7782  7839  /KING/CLARK              KING
MILLER    7934  7782  /KING/CLARK/MILLER       KING
JONES     7566  7839  /KING/JONES              KING
FORD      7902  7566  /KING/JONES/FORD         KING
SMITH     7369  7902  /KING/JONES/FORD/SMITH   KING
SCOTT     7788  7566  /KING/JONES/SCOTT        KING
ADAMS     7876  7788  /KING/JONES/SCOTT/ADAMS  KING

Same result using the recursive WITH clause:

WITH r_emps (ename, empno, mgr, path, root)
         AS (SELECT e.ename, e.empno, e.mgr
                  , '/' || e.ename AS path
                  , e.ename        AS root
               FROM emp e
              WHERE e.mgr IS NULL
             UNION ALL
             SELECT e.ename, e.empno, e.mgr
                  , m.path || '/' || e.ename AS path
                  , m.root
               FROM            emp    e
                    INNER JOIN r_emps m ON (m.empno = e.mgr))
SEARCH DEPTH FIRST BY ename SET sorting
SELECT e.ename, e.empno, e.mgr, e.path, e.root
  FROM r_emps e
 ORDER BY e.sorting
/

Path and root are set to ename in the anchor query. While the path is enlarged along the hierarchy by adding separator and path the root is simply passed all the way down to the leafs.