Control file structure in Oracle Apps

Control file structure in Oracle Apps

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: 

• In the $LOADTABLE pass the table to which you want to SQL the data
• In the $SCHEMA pass the schema in which the table is located.