SQL Snippets ™: Tutorials for Oracle Developers

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

insert into t values ( 'a'      , null   , 'A'      );
insert into t values ( 'b'      , null   , 'B'      );

insert into t values ( 'a.a'    , 'a'    , 'A.A'    );
insert into t values ( 'a.b'    , 'a'    , 'A.B'    );

insert into t values ( 'b.a'    , 'b'    , 'B.A'    );
insert into t values ( 'b.b'    , 'b'    , 'B.B'    );
insert into t values ( 'b.c'    , 'b'    , 'B.C'    );

insert into t values ( 'b.b.a'  , 'b.b'  , 'B.B.A'  );
insert into t values ( 'b.c.a'  , 'b.c'  , 'B.C.A'  );
insert into t values ( 'b.c.b'  , 'b.c'  , 'B.C.B'  );
insert into t values ( 'b.c.c'  , 'b.c'  , 'B.C.C'  );

insert into t values ( 'b.c.c.a', 'b.c.c', 'B.C.C.A');
insert into t values ( 'b.c.c.b', 'b.c.c', 'B.C.C.B');
insert into t values ( 'b.c.c.c', 'b.c.c', 'B.C.C.C');

commit;

column level       format 99999
column padded_key  format a15
column root_key    format a10
column root_name   format a10
column key_path    format a20
column name_path   format a20

set null '(null)'

variable v_target_key varchar2(10)