Hierarchical Selective Expansion

Setup

Run the code on this page in SQL*Plus to create the sample tables, data, etc. used by the examples in this section.

Be sure to read Using SQL Snippets ™ before executing any of these setup steps.

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)
 



Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-11721.html]SQL Snippets: Hierarchical Selective Expansion - Setup[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-11721.html">SQL Snippets: Hierarchical Selective Expansion - Setup</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Hierarchical Selective Expansion - Setup
  • URL (href): http://www.sqlsnippets.com/en/topic-11721.html