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)