SQL Snippets ™: Tutorials for Oracle Developers

set verify off

column num             format 999

column string          format a40
column string_2        format a40
column xml_instance    format a40

column val             format a10
column val_1           format a6
column val_2           format a6
column val_3           format a6
column val_1a          format a6
column val_2a          format a6
column val_3a          format a6
column val_1b          format a6
column val_2b          format a6
column val_3b          format a6

create table t1
(
  data_set varchar2(25) ,
  num      number(3)    ,
  string   varchar2(40) ,
  note     varchar2(25)
);

insert into t1 values ( 'Fixed Format', 1  , 'a    '           , 'One value'               );
insert into t1 values ( 'Fixed Format', 2  , 'a       '        , 'Two values, one empty'   );
insert into t1 values ( 'Fixed Format', 3  , 'a              ' , 'Three values, two empty' );
insert into t1 values ( 'Fixed Format', 4  , '     b,b  '      , 'Three values, two empty' );
insert into t1 values ( 'Fixed Format', 5  , '          c c c' , 'Three values, two empty' );
insert into t1 values ( 'Fixed Format', 6  , 'a    b,b  '      , 'Two values'              );
insert into t1 values ( 'Fixed Format', 7  , 'a    b,b  '      , 'Three values, one empty' );
insert into t1 values ( 'Fixed Format', 8  , 'a    b,b  c c c' , 'Three values, none empty');
insert into t1 values ( 'Fixed Format', 9  , '     '           , 'One empty value'         );
insert into t1 values ( 'Fixed Format', 10 , '          '      , 'Two values, two empty'   );
insert into t1 values ( 'Fixed Format', 11 , '               ' , 'Three values, all empty' );
insert into t1 values ( 'Fixed Format', 12 , null              , 'NULL string'             );

commit;

insert into t1 values ( 'Double Quotes', 1  , '"a"'                , 'One value'               );
insert into t1 values ( 'Double Quotes', 2  , '"a", ""'            , 'Two values, one empty'   );
insert into t1 values ( 'Double Quotes', 3  , '"a", "", ""'        , 'Three values, two empty' );
insert into t1 values ( 'Double Quotes', 4  , '"", "b,b", ""'      , 'Three values, two empty' );
insert into t1 values ( 'Double Quotes', 5  , '"", "", "c c c"'    , 'Three values, two empty' );
insert into t1 values ( 'Double Quotes', 6  , '"a", "b,b"'         , 'Two values'              );
insert into t1 values ( 'Double Quotes', 7  , '"a", "b,b", ""'     , 'Three values, one empty' );
insert into t1 values ( 'Double Quotes', 8  , '"a", "b,b", "c c c"', 'Three values, none empty');
insert into t1 values ( 'Double Quotes', 9  , '""'                 , 'One empty value'         );
insert into t1 values ( 'Double Quotes', 10 , '"", ""'             , 'Two values, two empty'   );
insert into t1 values ( 'Double Quotes', 11 , '"", "", ""'         , 'Three values, all empty' );
insert into t1 values ( 'Double Quotes', 12 , null                 , 'NULL string'             );

commit;

insert into t1
select 'Single Quotes', num, replace( string, '"', '''' ), note
from   t1
where  data_set = 'Double Quotes' ;

commit;

insert into t1
select 'Single Quotes No Commas', num, replace( string, ', ', ' ' ), note
from   t1
where  data_set = 'Single Quotes';

commit;

insert into t1
select 'CSV', num, replace( string, '"', null ), note
from   t1
where  data_set = 'Double Quotes';

update t1 set string = replace( string, 'b,b', 'bb' )
where  data_set = 'CSV'
and    string like '%b,b%' ;

update t1 set string = replace( string, ', ', ',' )
where  data_set = 'CSV' ;


delete from t1 where data_set = 'CSV' and num = 12 ;

commit;


insert into t1
select 'Tab Delimited', num, replace( string, '"', null ), note
from   t1
where  data_set = 'Double Quotes';

update t1 set string = replace( string, ', ', chr(9) )
where  data_set = 'Tab Delimited' ;

commit;


insert into t1
select 'Pipe Delimited', num, replace( string, '"', null ), note
from   t1
where  data_set = 'Double Quotes';

update t1 set string = replace( string, ', ', '|' )
where  data_set = 'Pipe Delimited' ;

commit;


insert into t1 values ( 'XML - 1', 1  , '<V>a</V>'                         , 'One value'               );
insert into t1 values ( 'XML - 1', 2  , '<V>a</V> <V></V>'                 , 'Two values, one empty'   );
insert into t1 values ( 'XML - 1', 3  , '<V>a</V> <V></V> <V></V>'         , 'Three values, two empty' );
insert into t1 values ( 'XML - 1', 4  , '<V></V> <V>b,b</V> <V></V>'       , 'Three values, two empty' );
insert into t1 values ( 'XML - 1', 5  , '<V></V> <V></V> <V>c c c</V>'     , 'Three values, two empty' );
insert into t1 values ( 'XML - 1', 6  , '<V>a</V> <V>b,b</V>'              , 'Two values'              );
insert into t1 values ( 'XML - 1', 7  , '<V>a</V> <V>b,b</V> <V></V>'      , 'Three values, one empty' );
insert into t1 values ( 'XML - 1', 8  , '<V>a</V> <V>b,b</V> <V>c c c</V>' , 'Three values, none empty');
insert into t1 values ( 'XML - 1', 9  , '<V></V>'                          , 'One empty value'         );
insert into t1 values ( 'XML - 1', 10 , '<V></V> <V></V>'                  , 'Two values, two empty'   );
insert into t1 values ( 'XML - 1', 11 , '<V></V> <V></V> <V></V>'          , 'Three values, all empty' );

commit;

insert into t1 values ( 'XML - 2', 1  , '<V1>a</V1>'                             , 'One value'               );
insert into t1 values ( 'XML - 2', 2  , '<V1>a</V1> <V2></V2>'                   , 'Two values, one empty'   );
insert into t1 values ( 'XML - 2', 3  , '<V1>a</V1> <V2></V2> <V3></V3>'         , 'Three values, two empty' );
insert into t1 values ( 'XML - 2', 4  , '<V1></V1> <V2>b,b</V2> <V3></V3>'       , 'Three values, two empty' );
insert into t1 values ( 'XML - 2', 5  , '<V1></V1> <V2></V2> <V3>c c c</V3>'     , 'Three values, two empty' );
insert into t1 values ( 'XML - 2', 6  , '<V1>a</V1> <V2>b,b</V2>'                , 'Two values'              );
insert into t1 values ( 'XML - 2', 7  , '<V1>a</V1> <V2>b,b</V2> <V3></V3>'      , 'Three values, one empty' );
insert into t1 values ( 'XML - 2', 8  , '<V1>a</V1> <V2>b,b</V2> <V3>c c c</V3>' , 'Three values, none empty');
insert into t1 values ( 'XML - 2', 9  , '<V1></V1>'                              , 'One empty value'         );
insert into t1 values ( 'XML - 2', 10 , '<V1></V1> <V2></V2>'                    , 'Two values, two empty'   );
insert into t1 values ( 'XML - 2', 11 , '<V1></V1> <V2></V2> <V3></V3>'          , 'Three values, all empty' );

commit;

create view t1_xml_1 as select * from t1 where data_set = 'XML - 1' ;

create view t1_xml_2 as select * from t1 where data_set = 'XML - 2' ;