-------------------------------------------------------------
-- SQL*Loader Control file generator
-- Works in SQL*Plus only!
--
-- Author: Mohit Dubey
-- Visit http://www.geocities.com/md_seraphin for more goodies!
--
-- This program is distributed under the GNU Public License Version 2
-- with the additional privisio that the original author's name and
-- contact details must be retained as-is in any modified or copied
-- versions of this program.
--
-- Output file:  {table_name}.ctl
-- 
-- NOTES:        Default choices for the file are as follows 
--               (alter to your needs): 
--     	         o Delimiter:              comma (',') 
--     	         o INFILE file extension:  .dat 
--     	         o DATE format:	           'MM/DD/YY' 
--  
--               It is possible to define the Loader Data Types 
--               of the other Data Types by revising the decode 
--               function pertaining to them. 
-- 
-------------------------------------------------------------


accept tname prompt 'Enter Table Name: '
  
set head off pau off feedback off timing off trimspool on pages 0 lines 132
set show off concat on verify off
spool &&tname..ctl 

SELECT  'LOAD DATA'||chr(10) 
||'INFILE '''||lower(table_name)||'.dat'' '||chr(10) 
||'INTO TABLE '||table_name||chr(10) 
||'FIELDS TERMINATED BY '','' '||chr(10)
||'OPTIONALLY ENCLOSED BY ''"'' '||chr(10)
||'TRAILING NULLCOLS'||chr(10) 
||'(' FROM user_tables 
WHERE TABLE_NAME = UPPER('&&tname'); 

SELECT decode(rownum,1,'   ',' , ')||
       rpad(column_name,33,' ')||
       decode(data_type, 'VARCHAR2',
               --'CHAR('||data_length||') '||decode(nullable,'N','','NULLIF('|| column_name ||'=BLANKS)'), 
               'CHAR('||data_length||')',
               'CHAR',
               --'CHAR('||data_length||')'||decode(nullable,'N','','NULLIF('|| column_name ||'=BLANKS)'), 
               'CHAR('||data_length||')',
               'FLOAT',
               --'DECIMAL EXTERNAL '||decode(nullable,'N','','NULLIF('||column_name||'=BLANKS)'), 
               'DECIMAL EXTERNAL ',
               'NUMBER',
               decode(nullable,'N','','NULLIF ('||column_name  ||'=BLANKS)'), 
               'DATE',
               --'DATE "MM/DD/YY" '||decode(nullable,'N','','NULLIF ('||column_name  ||'=BLANKS)'),
               'DATE "MM/DD/YY" ',
               'RAW',
               'RAW('||data_length||') '||decode(nullable,'N','','NULLIF('||column_name||' =BLANKS)')
               ,NULL) 
FROM user_tab_columns 
WHERE TABLE_NAME = UPPER('&&tname') 
ORDER BY COLUMN_ID; 

SELECT ')'  FROM sys.dual; 

spool off
set echo on;
-------------------------------------------------------------
-- End SQL*Loader Control file generator
-------------------------------------------------------------