SQL Snippets ™: Tutorials for Oracle Developers

set linesize 60

column source format a6
column name   format a4

create table t
(
  key varchar2(3) ,
  c1  varchar2(5) ,
  c2  varchar2(5) ,
  c3  varchar2(5) ,
  n1  number
);

insert into t values ( 'r1' , 'v1' , 'v2' , 'v3', 1  );
insert into t values ( 'r2' , 'v1' , 'v2' , null, 2  );
insert into t values ( 'r3' , 'v1' , null , 'v3', 3  );
insert into t values ( 'r4' , 'v1' , null , null, 4  );
insert into t values ( 'r5' , null , 'v2' , 'v3', 5  );
insert into t values ( 'r6' , null , 'v2' , null, 6  );
insert into t values ( 'r7' , null , null , 'v3', 7  );
insert into t values ( 'r8' , null , null , null, 8  );

commit;

create type name_value_pair as object( name varchar2(30), value varchar2(5) );
/

create type name_value_varray as varray(10) of name_value_pair ;
/

create type varray_type as varray(10) of varchar2(5);
/