Automatically Generate SQL Loader Control File Script

Script to generate SQL Loader control file with all columns from the table_name and in the same order columns are created in table


<pre class="brush: sql">

SELECT COL_NAME, LOADER_TYPE
FROM (
SELECT 'LOAD DATA
APPEND
INTO TABLE ' || '&TABLE_NAME' || ' FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY ''"''
TRAILING NULLCOLS
('
COL_NAME,
' ' LOADER_TYPE,
-999 SORTING_N
FROM DUAL
UNION ALL
SELECT COLUMN_NAME COL_NAME,
DECODE (
COLUMN_NAME,
'CREATED_BY', '"FND_GLOBAL.USER_ID"',
'CREATION_DATE', 'SYSDATE',
'LAST_UPDATED_BY', '"FND_GLOBAL.USER_ID"',
'LAST_UPDATE_DATE', 'SYSDATE',
DECODE (
DATA_TYPE,
'TIMESTAMP(6)', 'TIMESTAMP "YYYY-MM-DD HH24:MI:SS.FF",',
'NUMBER', 'DECIMAL EXTERNAL,',
'VARCHAR2', 'CHAR "TRIM(:' || COLUMN_NAME || ')",',
'CHAR', 'CHAR',
'DATE', '"TO_DATE(SUBSTR(:'
|| COLUMN_NAME
|| ',1,19),''YYYY-MM-DD HH24:MI:SS'')",'))
LOADER_TYPE,
COLUMN_ID SORTING_N
FROM ALL_TAB_COLS
WHERE OWNER = UPPER ('&SCHEMA_NAME') AND TABLE_NAME = UPPER ('&TABLE_NAME')
UNION ALL
SELECT ')' COL_NAME, '' LOADER_TYPE, 10000 SORTING_N FROM DUAL
)
ORDER BY SORTING_N

</pre>