Showing posts with label Quickest way to generate the columns used in the ctl file. Show all posts
Showing posts with label Quickest way to generate the columns used in the ctl file. Show all posts

Monday, August 25, 2008

Quickest way to generate the columns used in the ctl file

Select decode (column_id, 1, ' ', ' , ') ||
rpad (column_name, 33, ' ') ||
decode (data_type,
'VARCHAR2', 'CHAR NULLIF ('||column_name||'=BLANKS)',
'FLOAT', 'DECIMAL EXTERNAL NULLIF('||column_name||'=BLANKS)',
'NUMBER', decode (data_precision, 0,
'INTEGER EXTERNAL NULLIF ('||column_name||
'=BLANKS)', decode (data_scale, 0,
'INTEGER EXTERNAL NULLIF ('||
column_name||'=BLANKS)',
'DECIMAL EXTERNAL NULLIF ('||
column_name||'=BLANKS)')),
'DATE', 'DATE "mm/dd/yy" NULLIF ('||
column_name||'=BLANKS)', null)
from all_tab_columns
where table_name = upper ('$LOADTABLE')
and owner = upper ('$SCHEMA')
order by column_id;

Note:
1. In the $LOADTABLE pass the table to which you want to SQL the data
2. In the $SCHEMA pass the schema in which the table is located.