Sunday, October 4, 2009

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;

No comments:

Post a Comment

Blog Archive