Oracle Script to unload/load a table

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_table.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