Full Hierarchy

Whilst the CONNECT BY functionality has always been nifty, its been a major chore to actually obtain the full hierarchy for a particular node in a hierarchy, often requiring either some PL/SQL or a temporary result set to do it.

9i has a new function SYS_CONNECT_BY_PATH, which is probably best explained with the example from the documentation

SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM employees
   START WITH last_name = 'Kochhar'
   CONNECT BY PRIOR employee_id = manager_id;

Path
---------------------------------------------------------------
 /Kochhar
   /Kochhar/Greenberg
     /Kochhar/Greenberg/Faviet
     /Kochhar/Greenberg/Chen
     /Kochhar/Greenberg/Sciarra
     /Kochhar/Greenberg/Urman
     /Kochhar/Greenberg/Popp
   /Kochhar/Whalen
   /Kochhar/Marvis
   /Kochhar/Baer
   /Kochhar/Higgens
     /Kochhar/Higgens/Gietz