SQL Snippets ™: Tutorials for Oracle Developers

column val format a10


create table t
(
  key  varchar2(3) ,
  c    varchar2(10) ,
  note varchar2(25)
);

insert into t values ( 'r01' , 'v1'       , 'One value'               );
insert into t values ( 'r02' , 'v1,'      , 'Two values, one empty'   );
insert into t values ( 'r03' , 'v1,,'     , 'Three values, two empty' );
insert into t values ( 'r04' , ',v2,'     , 'Three values, two empty' );
insert into t values ( 'r05' , ',,v3'     , 'Three values, two empty' );
insert into t values ( 'r06' , 'v1,v2'    , 'Two values'              );
insert into t values ( 'r07' , 'v1,v2,'   , 'Three values, one empty' );
insert into t values ( 'r08' , 'v1,v2,v3' , 'Three values, none empty');
insert into t values ( 'r09' , null       , 'One empty value'         );
insert into t values ( 'r10' , ','        , 'Two values, two empty'   );
insert into t values ( 'r11' , ',,'       , 'Three values, all empty' );

commit;

create table t2
(
  key  varchar2(3) ,
  c    varchar2(15) ,
  note varchar2(25)
);

insert into t2 values ( 'r01' , q'{'v1'}'           , 'One value'               );
insert into t2 values ( 'r02' , q'{'v1',''}'        , 'Two values, one empty'   );
insert into t2 values ( 'r03' , q'{'v1','',''}'     , 'Three values, two empty' );
insert into t2 values ( 'r04' , q'{'','v2',''}'     , 'Three values, two empty' );
insert into t2 values ( 'r05' , q'{'','','v3'}'     , 'Three values, two empty' );
insert into t2 values ( 'r06' , q'{'v1','v2'}'      , 'Two values'              );
insert into t2 values ( 'r07' , q'{'v1','v2',''}'   , 'Three values, one empty' );
insert into t2 values ( 'r08' , q'{'v1','v2','v3'}' , 'Three values, none empty');
insert into t2 values ( 'r09' , q'{''}'             , 'One empty value'         );
insert into t2 values ( 'r10' , q'{'',''}'          , 'Two values, two empty'   );
insert into t2 values ( 'r11' , q'{'','',''}'       , 'Three values, all empty' );
insert into t2 values ( 'r12' , null                , 'NULL value'              );

commit;

create table t3
(
  key  varchar2(3) ,
  c    varchar2(10) ,
  note varchar2(25)
);

insert into t3 values ( 'r01' , '1'     , 'One value'    );
insert into t3 values ( 'r02' , '1,2'   , 'Two values'   );
insert into t3 values ( 'r03' , '1,2,3' , 'Three values' );
insert into t3 values ( 'r04' , null    , 'NULL value'   );

commit;

create table t5
(
  key  varchar2(10) ,
  c    varchar2(35) ,
  note varchar2(25)
);

insert into t5 values ('r01','<V1>a</V1>'                      , 'One value'               );
insert into t5 values ('r02','<V1>a</V1> <V2></V2>'            , 'Two values, one empty'   );
insert into t5 values ('r03','<V1>a</V1> <V2></V2>  <V3></V3>' , 'Three values, two empty' );
insert into t5 values ('r04','<V1></V1> <V2>b</V2> <V3></V3>'  , 'Three values, two empty' );
insert into t5 values ('r05','<V1></V1> <V2></V2>  <V3>c</V3>' , 'Three values, two empty' );
insert into t5 values ('r06','<V1>a</V1> <V2>b</V2>'           , 'Two values'              );
insert into t5 values ('r07','<V1>a</V1> <V2>b</V2> <V3></V3>' , 'Three values, one empty' );
insert into t5 values ('r08','<V1>a</V1> <V2>b</V2> <V3>c</V3>', 'Three values, none empty');
insert into t5 values ('r09','<V1></V1>'                       , 'One empty value'         );
insert into t5 values ('r10','<V1></V1> <V2></V2>'             , 'Two values, two empty'   );
insert into t5 values ('r11','<V1></V1> <V2></V2>  <V3></V3>'  , 'Three values, all empty' );
insert into t5 values ('r12',null                              , 'NULL string'             );

commit;

create table t6 
( 
  key number,
  c   varchar2(10)
);

insert into t6 values ( 1, 'v1' );
insert into t6 values ( 2, 'v2' );
insert into t6 values ( 3, 'v3' );
insert into t6 values ( 4, 'v4' );
insert into t6 values ( 5, 'v5' );

commit;

create table integers( integer_value integer primary key )
organization index ;

insert into integers values ( 1 ) ;
insert into integers values ( 2 ) ;
insert into integers values ( 3 ) ;
insert into integers values ( 4 ) ;
insert into integers values ( 5 ) ;
insert into integers values ( 6 ) ;
insert into integers values ( 7 ) ;
insert into integers values ( 8 ) ;
insert into integers values ( 9 ) ;
insert into integers values ( 10) ;

commit ;


create type varchar2_varray_type is varray(10) of varchar2(4000) ;
/

create type integer_varray_type is varray(10) of number(38) ;
/