Script for creating SQL Loader control file

This is script is helpful to create SQL Loader control file for any given table. All you need to do is just removing a comma from last but one line. Also change the delimiter as necessary.


/* GenerateControlFile.sql
Generates SQL Loader control file from data dictionary
Takes table name and schema name as input
Generated control file is complete,
just remove the command from last but one line
Apply any data processing during load manually in control file, if required
*/
select
'LOAD DATA
APPEND
INTO TABLE ' || '&TABLE_NAME' ||
' FIELDS TERMINATED BY "~"
TRAILING NULLCOLS
(' "Column Name",' ' "sql_loader_type" from dual
union all
select
COLUMN_NAME,
DECODE(DATA_TYPE,
'TIMESTAMP(6)','TIMESTAMP "YYYY-MM-DD HH24:MI:SS.FF",',
'NUMBER','DECIMAL EXTERNAL,',
'VARCHAR2','CHAR,',
'CHAR','CHAR',
'DATE','"TO_DATE(SUBSTR(:' || column_name || ',1,19),''YYYY-MM-DD HH24:MI:SS'')",'
) "sql_loader_type"
from all_tab_cols
where owner=UPPER('&SCHEMA_NAME') AND TABLE_NAME = UPPER('&TABLE_NAME')
union all
select ')' "Column Name" , '' "sql_loader_type" from dual