SQL Snippets ™: Tutorials for Oracle Developers

create table t
( key        varchar2(10) ,
  parent_key varchar2(10) ,
  name       varchar2(10)
);

insert into t values ( 'nls'   , null     , 'NLS'    );
insert into t values ( 'demo'  , 'nls'    , 'DATA'   );
insert into t values ( 'mesg'  , 'nls'    , 'DEMO'   );

insert into t values ( 'server', null     , 'SERVER' );
insert into t values ( 'bin'   , 'server' , 'BIN'    );
insert into t values ( 'config', 'server' , 'CONFIG' );
insert into t values ( 'log'   , 'config' , 'LOG'    );
insert into t values ( 'ctx'   , 'server' , 'CTX'    );
insert into t values ( 'admin' , 'ctx'    , 'ADMIN'  );
insert into t values ( 'data'  , 'ctx'    , 'DATA'   );
insert into t values ( 'delx'  , 'data'   , 'DELX'   );
insert into t values ( 'enlx'  , 'data'   , 'ENLX'   );
insert into t values ( 'eslx'  , 'data'   , 'ESLX'   );
insert into t values ( 'mig'   , 'ctx'    , 'MESG'   );

commit;

column level       format 99999
column key_indented  format a15
column root_key    format a10
column root_name   format a10
column key_path    format a25
column name_path   format a25

set null '(null)'

variable v_target_key varchar2(10)