Retrieving a Path with SYS_CONNECT_BY_PATH v12

SYS_CONNECT_BY_PATH is a function that works within a hierarchical query to retrieve the column values of a specified column that occur between the current node and the root node. The signature of the function is:

SYS_CONNECT_BY_PATH (<column>, <delimiter>)

The function takes two arguments:

column is the name of a column that resides within a table specified in the hierarchical query that is calling the function.

delimiter is the varchar value that separates each entry in the specified column.

The following example returns a list of employee names, and their managers; if the manager has a manager, that name is appended to the result:

edb=# SELECT level, ename , SYS_CONNECT_BY_PATH(ename, '/') managers
      FROM emp
      CONNECT BY PRIOR empno = mgr
      START WITH mgr IS NULL
      ORDER BY level, ename, managers;

 level | ename  |        managers
-------+--------+-------------------------
     1 | KING   | /KING
     2 | BLAKE  | /KING/BLAKE
     2 | CLARK  | /KING/CLARK
     2 | JONES  | /KING/JONES
     3 | ALLEN  | /KING/BLAKE/ALLEN
     3 | FORD   | /KING/JONES/FORD
     3 | JAMES  | /KING/BLAKE/JAMES
     3 | MARTIN | /KING/BLAKE/MARTIN
     3 | MILLER | /KING/CLARK/MILLER
     3 | SCOTT  | /KING/JONES/SCOTT
     3 | TURNER | /KING/BLAKE/TURNER
     3 | WARD   | /KING/BLAKE/WARD
     4 | ADAMS  | /KING/JONES/SCOTT/ADAMS
     4 | SMITH  | /KING/JONES/FORD/SMITH
(14 rows)

Within the result set:

• The level column displays the number of levels that the query returned.
• The ename column displays the employee name.
• The managers column contains the hierarchical list of managers.

The Advanced Server implementation of SYS_CONNECT_BY_PATH does not support use of:

• SYS_CONNECT_BY_PATH inside CONNECT_BY_PATH
• SYS_CONNECT_BY_PATH inside SYS_CONNECT_BY_PATH