REM  script6-2.sql (unload.sql)
REM  
REM  Reads data dictionary description of a table to generate another
REM     SQL*Plus script that will unload the contents of a table to a 
REM     flat file.  It then generates a corresponding SQL*Loader control
REM     file for re-loading the data.
REM
REM  Known problems:
REM      Does not know handle LONG, RAW, LONG RAW or other "exotic"
REM        datatypes.  Prints "ERROR" if another datatype is encountered.
REM
REM
set tab off heading off heading off feedback off echo off verify off
set 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 -
    'How many TOTAL digits for numbers without defined precision? '
ACCEPT default_scale PROMPT -
    'How many DECIMAL digits 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 a 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