How To Create SQL Loader Control File Dynamically In Oracle

Generate SQL Loader Control File Dynamically

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.

Advertisement

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:

Advertisement
LOAD DATA                                                                                          
APPEND INTO TABLE EMP                                                                        
FIELDS TERMINATED BY ','  OPTIONALLY ENCLOSED BY '"'                                             
TRAILING NULLCOLS (
EMPNO,                                                                                             
ENAME,                                                                                             
JOB,                                                                                               
MGR,                                                                                               
HIREDATE,                                                                                          
SAL,                                                                                               
COMM,                                                                                              
DEPTNO                                                                                            
)

See also:

  1. SQL Loader Shell Script Example
  2. Run SQL Loader From Command Prompt
  3. Import CSV in Oracle Using SQL Loader
  4. SQL Loader Control File Example
Related:  Oracle TO_CHAR Date Format Examples

Vinish Kapoor

An Oracle Apex Consultant, Oracle ACE, and founder of foxinfotech.in and orclqa.com a question and answer forum for developers.

This Post Has 2 Comments

  1. V. Kapoor 29 Sep 2016

    Use the below query:

    SELECT 'LOAD DATA
    APPEND INTO TABLE '
    || '&tblname'
    || '
    FIELDS TERMINATED BY '','' OPTIONALLY ENCLOSED BY '''
    || CHR (34)
    || '''
    TRAILING NULLCOLS ('
    FROM DUAL
    UNION ALL
    SELECT CASE WHEN rn = c THEN RTRIM (tab_cols, ',') ELSE tab_cols END tab_cols
    FROM ( SELECT column_name || ',' tab_cols,
    ROWNUM rn,
    (SELECT MAX (column_id)
    FROM user_tab_cols
    WHERE table_name = '&tblname')
    c
    FROM user_tab_cols
    WHERE table_name = '&tblname'
    ORDER BY column_id)
    UNION ALL
    SELECT ')' FROM DUAL

  2. ArunRaj V 29 Sep 2016

    How to remove the last comma from the column select list

Comments are closed.