In my previous blog I posted to Generate SQL Plus script Dynamically and now I am providing a SQL Script to generate SQL Loader Control File dynamically from SQL script. You need to just run this script in the schema where that table exists for which you want to create the control file and after running the script it will prompt you to enter the Table name and after entering the table name it will generate the Control file for that table. You may need to edit control file after generation but a basic Control File would be created, which could be a very time-saving.
Below is the SQL Plus Script from which you can generate the Control File:
SET ECHO OFF; SET HEAD OFF; SET PAGESIZE 100; SET LINESIZE 100; SET UNDERLINE OFF; SET FEED OFF; SET VER OFF; ACCEPT tblname CHAR PROMPT 'Enter Table Name: '; SPOOL yourtable.ctl SELECT 'LOAD DATA APPEND INTO TABLE ' || '&tblname' || ' FIELDS TERMINATED BY '','' OPTIONALLY ENCLOSED BY ''' || CHR (34) || ''' TRAILING NULLCOLS (' FROM DUAL UNION ALL SELECT tab_cols FROM ( SELECT column_name || ',' tab_cols FROM user_tab_cols WHERE table_name = '&tblname' ORDER BY column_id) UNION ALL SELECT ')' FROM DUAL / SPOOL OFF /
Suppose if you will generate it for Scott.Emp Table, then the output of the control file would be as following:
LOAD DATA APPEND INTO TABLE EMP FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO )