
-------------------------------------------------------------------------
--    SQL Script Name:  unload_fixed.sql
--
--    Function:         generates a sql*plus script to unload a table to a
--                      file and a SQL*Loader script to reload the same
--                      data.  Intent is to create a faster alternative
--                      to export/import.
--
--                      Initial testing indicates that the unload takes about
--                      20% longer than EXP, but that the reload takes only
--                      45% as long as IMP.  Under Oracle7 r7.1, the capability
--                      of parallelizing direct loads (but not IMP) should 
--                      provide much faster load times, reasonably 10% of the
--                      time for IMP.  
--
--                      WORD OF WARNING Re: PERFORMANCE:
--                      Performance is very specific to the data distribution
--                      of the table data.  Much poorer performance has been
--                      seen in the following cases:
--                       -  many long varchar columns which actually contain
--                          short data values;
--                       -  many number columns without precision or scale
--                          which are defaulted to large numbers;
--                       -  lots of null values;
--                      All of these cases lead to inefficient use of the
--                      fixed record format of the unload file.  Padding the
--                      unload file with leading/trailing zeros or trailing
--                      blanks yields files 4X the size of an export dmp file
--                      and unload times 4X those of export.  (Even in these
--                      extreme test situations, the load time was still 
--                      between 80% and 90% of IMP.)
--
--
--                      This unload/reload utility has some other advantages
--                      besides speed.  The unload can easily select a subset
--                      of the original table (for statistical sampling or
--                      retrieving rows for a particular department or busines
--                      date for instance) whereas EXP/IMP deals with entire
--                      tables.  Additionally, if desired, unload can sort
--                      the output in order to speed index builds and/or
--                      optimize cache hits on the reloaded data based on
--                      loading frequently used rows contiguously.  This may
--                      provide an additional benefit in some reorg efforts.
--                      Finally, the unload might have a GROUP BY appended
--                      which would facilitate development of summary tables.
--
--                      By editing the generated unload2.sql and SQL*Loader
--                      .CTL scripts, one could additionally remove columns
--                      or modify them with SQL (or PL/SQL functions in r7.1)
--                      during the extract.  Just be sure to adjust the length
--                      of the COLUMN and PAGESIZE in unload2.sql and the
--                      input field in .CTL to reflect whatever changes.
--
--                      This utility can also unload data from a view which
--                      is not possible via EXP.  This facility may be used
--                      to do subsets--selection (specific rows), projection 
--                      (specific columns), joins, GROUP BY summaries or
--                      function application without having to edit this
--                      script or its generated output scripts.
--
--    Arguments IN:     prompts for table_owner, table_name as well as
--                      default_precision and default_scale for columns
--                      defined as NUMBER or FLOAT (without precision or
--                      scale defined).
--
--    Arguments OUT:    none
--
--    Calls:            none
--
--    Called by:        none
--
--    Change History:   05/25/94  gcdodge   original development
--
--    Limitations:      Doesn't handle long, raw, long raw, rowid, mlslabel
--                      datatypes.
--
--                      This utility has been tested in a Solaris 2.3 
--                      environment, but is expected to be fully portable
--                      to any ASCII platform.  Unlike EXP/IMP, however,
--                      it will not automatically make translations when
--                      the unload file is moved from ASCII to EBCDIC or
--                      vice versa.  Since all data is written in external
--                      formats, one should expect that file transfer 
--                      utilities that do such conversions should work.
--                      As an alternative, one could edit the SQL*Loader
--                      .CTL script to specify the alternative encoding
--                      scheme.
--
--                      If a numeric column is encountered which has no
--                      defined precision or scale, then this script will
--                      use default values (prompted for); this poses three
--                      risks: 1) that you may overspecify the precision
--                      and thereby waste space in the unload file; 2)
--                      you may underspecify the precision and thereby
--                      get overflow indicators in the unloaded data which
--                      may not be caught prior to loading; 3) you may
--                      underspecify the scale and introduce truncation
--                      which will not be found by either the unload or
--                      load processes.  For this reason, it is strongly
--                      recommended that numeric table columns be defined
--                      with appropriate precision and scale values.
--
--                      The generated SQL*Loader script assumes that fields
--                      of blanks should be loaded as NULLS...if the table
--                      has columns for which SPACES are valid values, then
--                      it will be necessary to edit the generated unload2.sql
--                      script to concatenate double quotes before and after
--                      the affected column(s) along with changing the length
--                      (pagesize in unload2.sql and the individual field's
--                      length in the generated .CTL file) by two bytes.
--
-------------------------------------------------------------------------
set tab off
set heading off heading off feedback off echo off verify off space 1 pagesize 0 linesize 120
accept owner             prompt 'What schema owns the table to be unloaded? '
accept table_name        prompt 'What table is to be unloaded? '
accept default_precision prompt 'What TOTAL number of digits should be reserved for numbers without defined precision? '
accept default_scale     prompt 'What number of DECIMAL digits should be reserved for numbers without defined scale? '
---------------------------------------------------
--  Generate the unload script
---------------------------------------------------
spool unload_fixed2.sql
select 'SET HEADING OFF FEEDBACK OFF ECHO OFF VERIFY OFF SPACE 0 PAGESIZE 0 TERMOUT OFF'
  from dual
/

--  Calculate the sum of all output field lengths and set the output record size
select 'SET LINESIZE ' 
       || (sum(decode(data_type,
                      'CHAR',data_length,
                      'VARCHAR',data_length,
                      'VARCHAR2',data_length,
                      'DATE',14,
                      'NUMBER',decode(data_precision,
                                      '',&default_precision+2,
                                      greatest(data_precision-data_scale,1)+decode(data_scale,0,0,1)+data_scale)+1,
                      'FLOAT',&default_precision+2,
                      data_length)))
  from dba_tab_columns
 where owner=upper('&&owner')
   and table_name=upper('&&table_name')
/

--  Generate an appropriate SQL*Plus COLUMN command to control formatting of each output field
select 'COLUMN ' || rpad('"'||column_name||'"',32) 
       || ' FORMAT ' 
       || rpad(decode(data_type,
                   'CHAR','A'||data_length,
                   'VARCHAR2','A'||data_length,
                   'VARCHAR','A'||data_length,
                   'DATE','A14',
                   'NUMBER',decode(data_precision,
                                   '', rpad('0',&default_precision-&default_scale,'9')||'.'||rpad('9',&default_scale,'9'),
                                   rpad('0',greatest(data_precision-data_scale,1),'9') || decode(data_scale,0,'','.')
                                       || decode(data_scale,0,'',rpad('9',data_scale,'9'))),
                   'FLOAT',rpad('0',&default_precision-&default_scale,'9')||'.'||rpad('9',&default_scale,'9'),
                   'ERROR'),40)
       || ' HEADING ''X'''
  from dba_tab_columns
 where owner=upper('&&owner')
   and table_name=upper('&&table_name')
 order by column_id
/

--  Generate the actual SELECT statement to unload table data
select 'SPOOL /tmp/&&owner..&&table_name..DAT'
  from dual
/
column var1 noprint
column var2 noprint
select 'a' var1, 0 var2, 'SELECT '
  from dual
union
select 'b', column_id, decode(column_id, 1, '    ', '  , ')
                       || decode(data_type,'DATE','to_char('||'"'||column_name||'"'||',''YYYYMMDDHH24MISS'') '||'"'||column_name||'"'  ,
                                            '"'||column_name||'"')
  from dba_tab_columns
 where owner=upper('&&owner')
   and table_name=upper('&&table_name')
union
select 'c', 0, 'FROM &&owner..&&table_name'
  from dual
union
select 'd', 0, ';'
  from dual
 order by 1,2
/
select 'SPOOL OFF'
  from dual
/
select 'SET TERMOUT ON'
  from dual
/

spool off
-----------------------------------------------------------------------------
--  Generate the SQL*Loader control file
-----------------------------------------------------------------------------
set lines 120 pages 0
spool &&owner..&&table_name..CTL
select 'a' var1, 0 var2, 'OPTIONS(DIRECT=TRUE)'
  from dual
union
select 'b', 0, 'LOAD DATA'
  from dual
union
select 'c', 0, 'INFILE  ''/tmp/&&owner..&&table_name..DAT'''
  from dual
union
select 'd', 0, 'BADFILE  &&owner..&&table_name..BAD'
  from dual
union
select 'e', 0, 'DISCARDFILE  &&owner..&&table_name..DSC'
  from dual
union
select 'f', 0, 'DISCARDMAX 999'
  from dual
union
select 'm', 0, 'INTO TABLE &&owner..&&table_name'
  from dual
union
select 'n', column_id, rpad(decode(column_id,1,'(',',')||'"'||column_name||'"',31)
                       || decode(data_type,
                                 'CHAR','CHAR('||data_length||')',
                                 'VARCHAR','CHAR('||data_length||')',
                                 'VARCHAR2','CHAR('||data_length||')',
                                 'DATE','DATE(14) "YYYYMMDDHH24MISS"',
                                 'NUMBER','DECIMAL EXTERNAL('||decode(data_precision,
                                                          '',&default_precision+2,
                                                          greatest(data_precision-data_scale,1)+decode(data_scale,0,0,1)+data_scale+1)
                                                 ||')',
                                 'FLOAT','DECIMAL EXTERNAL('||to_char(&default_precision+2)||')',
                                 'ERROR--'||data_type)
                       || ' NULLIF ("' ||column_name||'" = BLANKS)'
  from dba_tab_columns
 where owner = upper('&&owner')
   and table_name = upper('&&table_name')
union
select 'z', 0, ')'
  from dual
 order by 1, 2
/

spool off

-----------------------------------------------------------------------------
--  Cleanup
-----------------------------------------------------------------------------
clear column
clear break
clear compute
undef owner
undef table_name
undef default_precision
undef default_scale