The SYS_CONNECT_BY_PATH function returns a single string containing all the column values encountered in the path from root to node.
select
lpad(' ', level-1 ) || key as key_indented ,
name ,
SYS_CONNECT_BY_PATH( key , '/' ) as key_path ,
SYS_CONNECT_BY_PATH( name, '/' ) as name_path
from
t
start with
parent_key is null
connect by
parent_key = prior key
;
KEY_INDENTED NAME KEY_PATH NAME_PATH --------------- ---------- ------------------------- ------------------------- nls NLS /nls /NLS demo DATA /nls/demo /NLS/DATA mesg DEMO /nls/mesg /NLS/DEMO server SERVER /server /SERVER bin BIN /server/bin /SERVER/BIN config CONFIG /server/config /SERVER/CONFIG log LOG /server/config/log /SERVER/CONFIG/LOG ctx CTX /server/ctx /SERVER/CTX admin ADMIN /server/ctx/admin /SERVER/CTX/ADMIN data DATA /server/ctx/data /SERVER/CTX/DATA delx DELX /server/ctx/data/delx /SERVER/CTX/DATA/DELX enlx ENLX /server/ctx/data/enlx /SERVER/CTX/DATA/ENLX eslx ESLX /server/ctx/data/eslx /SERVER/CTX/DATA/ESLX mig MESG /server/ctx/mig /SERVER/CTX/MESG
SYS_CONNECT_BY_PATH is only available in Oracle version 9i or greater. For earlier versions a custom, recursive PL/SQL function can be used in place of SYS_CONNECT_BY_PATH.
------------------------------------------------------------ -- Note: -- -- This function is only for demonstration purposes. -- In a real application more robust code would be needed -- to guard against things like separator characters -- appearing in KEY values, hierarchical loops in the data, -- etc. ------------------------------------------------------------ create or replace function KEY_PATH ( p_key t.key%type , p_separator varchar2 default '/' ) return varchar2 is v_parent_key t.parent_key%type ; v_key t.key%type ; begin select parent_key, key into v_parent_key, v_key from t where key = p_key ; if v_parent_key is null then return ( p_separator || v_key ); else return ( KEY_PATH( v_parent_key, p_separator ) || p_separator || v_key ); end if; exception when no_data_found then return( null ); end; / show errors No errors.
select
lpad(' ', level-1 ) || key as key_indented ,
name ,
KEY_PATH( key, '/' ) as KEY_PATH
from
t
start with
parent_key is null
connect by
parent_key = prior key
;
KEY_INDENTED NAME KEY_PATH --------------- ---------- ------------------------- nls NLS /nls demo DATA /nls/demo mesg DEMO /nls/mesg server SERVER /server bin BIN /server/bin config CONFIG /server/config log LOG /server/config/log ctx CTX /server/ctx admin ADMIN /server/ctx/admin data DATA /server/ctx/data delx DELX /server/ctx/data/delx enlx ENLX /server/ctx/data/enlx eslx ESLX /server/ctx/data/eslx mig MESG /server/ctx/mig
With this approach an additional function would be needed if the path for another column, like NAME, were required.
If no hierarchical information, e.g. LEVEL, other than a path is required then the START WITH and CONNECT BY clauses can be omitted since KEY_PATH already knows how to traverse the hierarchy.
select key , name , KEY_PATH( key, '/' ) as KEY_PATH from t order by KEY_PATH ;
KEY NAME KEY_PATH ---------- ---------- ------------------------- nls NLS /nls demo DATA /nls/demo mesg DEMO /nls/mesg server SERVER /server bin BIN /server/bin config CONFIG /server/config log LOG /server/config/log ctx CTX /server/ctx admin ADMIN /server/ctx/admin data DATA /server/ctx/data delx DELX /server/ctx/data/delx enlx ENLX /server/ctx/data/enlx eslx ESLX /server/ctx/data/eslx mig MESG /server/ctx/mig