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;