Title: Exporting Table Data to SQL*Loader Files

Author: Grzegorz Galka, a software engineer for
      Winuel SA, in Wroclaw, Poland.

      This submission consists of 3 scripts: ExportTable.sql, GetLoaderControl.sql,
      GetLoaderData.sql (can be joined). Those scripts retrieve data from a given table and
      save it to ascii file in CSV format (interpreted in MS Excel). A simple control file is
      created too, which may be used by SQL*Loader to import this CSV file. Usage:
      SQL>@ExportTable

em file:       ExportTable.sql
      rem called by:  User
      rem purpose:    Export table data to SQLLoader files
      accept TableName prompt 'Table to export:'
      set concat ~
      prompt Data file  - &TableName~.csv
      prompt Control file - &TableName~.ctl
      spool &TableName~.sql
      start GetLoaderData &TableName
      spool &TableName.csv
      start &TableName
      spool &TableName~.ctl
      start GetLoaderControl &TableName
      spool off
      host del &TableName~.sql
      rem host rm &TableName~.sql
      set termout on


      rem file:       GetLoaderControl.sql
      rem called by:  ExportTable.sql
      rem purpose:    Create SQLLoader control file
      set pause off
      set newpage none
      set heading off
      set concat ~
      set feedback off
      set verify off
      set linesize 80
      set trimspool on
      set trimout off
      set termout off
      column ord noprint
      select 1 ord,'load data' from dual
      union
      select 2 ord,'infile &&TableName.csv' from  dual
      union
      select 3 ord,'append' from dual
      union
      select 4 ord,'into table &TableName' from dual
      union
      select 5 ord,'fields terminated by ' || ''';''' from dual
      union
      select 6 ord,'(' from dual
      union
      select 10*column_id ord, column_name || ' ' ||
             decode(data_type,
                    'NUMBER','decimal external',
                    'VARCHAR2','char',
                    'CHAR','char',
                    'DATE','date') || ','
        from user_tab_columns
       where table_name = upper('&TableName')
         and column_id not in ( select max(column_id)
                                  from user_tab_columns
                                 where table_name = upper('&TableName') )
      union
      select 1000*column_id ord,column_name || ' ' ||
             decode(data_type,
                    'NUMBER','decimal external',
                    'VARCHAR2','char',
                    'CHAR','char',
                    'DATE','date') || ')'
        from user_tab_columns
       where table_name=upper('&TableName')
         and column_id in ( select max(column_id)
                              from user_tab_columns
                             where table_name = upper('&TableName') )
      order by ord;


      rem File:       GetLoaderData.sql
      rem Called by:  ExportTable.sql
      rem Purpose:    Create script extracting data from table
      set pause off
      set newpage none
      set heading off
      set concat ~
      set feedback off
      set verify off
      set linesize 1000
      set trimspool on
      set trimout on
      set termout off
      column ord noprint
      select 0 ord, 'select',null,null,'rtrim('||column_name ||')'
        from user_tab_columns
       where table_name = upper('&&TableName')
         and column_id=1
      union
      select column_id ord, '||' , ''';''' , '||' ,'rtrim('||column_name ||')'
        from user_tab_columns
       where table_name = upper('&TableName')
         and column_id > 1
      union
      select 1000 ord, '||' , ''';''' , null, 'from &TableName order by 1;'
        from dual
      order by ord;