SCRIPT TO GENERATE SQL*LOADER CONTROL FILE

This script prepares a SQL*Loader control file for a table already existing in the database. The script accepts
the table name and automatically creates a file with the table name and extension ‘ctl’.  This is specially
useful if you have the DDL statement to create a particular table and have a free-format ASCII-delimited file but
have not yet created a SQL*Loader control file for the loading operation.

Default choices for the file are as follows (alter to your needs):

Delimiter:              comma (‘,’)
INFILE file extension:  .dat
DATE format:            ‘MM/DD/YY’

You may define the Loader Data Types of the other Data Types by revising the DECODE function pertaining
to them.

Please note:
The name of the table to be unloaded needs to be provided when the script is executed as follows:

Script:

set echo off
set heading off
set verify off
set feedback off
set show off
set trim off
set pages 0
set concat on
set lines 300
set trimspool on
set trimout on

spool &1..ctl

select 'LOAD DATA'||chr (10)||
       'INFILE '''||lower (table_name)||'.dat'''||chr (10)||
       'INTO TABLE '||table_name||chr (10)||
       'FIELDS TERMINATED BY '','''||chr (10)||
       'TRAILING NULLCOLS'||chr (10)||'('
from   all_tables
where  table_name = upper ('&1');

select decode (rownum, 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   user_tab_columns
where  table_name = upper ('&1')
order  by column_id;

select ')'
from sys.dual;
spool off

Sample Output:

LOAD DATA
INFILE 'tv.dat'
INTO TABLE TV
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(  T1                               INTEGER EXTERNAL NULLIF (T1=BLANKS)
 , T2                               CHAR NULLIF (T2=BLANKS)
 , T3                               CHAR NULLIF (T3=BLANKS)
)

Posted

in

by

Tags:

Comments

3 responses to “SCRIPT TO GENERATE SQL*LOADER CONTROL FILE”

  1. sysdljr Avatar
    sysdljr

    把上面的脚本复制到文件中, 调用 sqlplus执行后输出的结果如下,还需要手工处理Enter value for 1: dept ?

    Enter value for 1: dept
    LOAD DATA
    INFILE ‘dept.dat’
    INTO TABLE DEPT
    FIELDS TERMINATED BY ‘,’
    TRAILING NULLCOLS
    (

    Enter value for 1: dept
    DEPTNO INTEGER EXTERNAL NULLIF (DEPTNO=BLANKS)
    , DNAME CHAR NULLIF (DNAME=BLANKS)
    , LOC CHAR NULLIF (LOC=BLANKS)
    )

    1. Maclean Liu Avatar

      是的,还需要手工定制一下的。

  2. Fabricio Avatar
    Fabricio

    Thanks for the script. Very usefull when generating control files from big tables.
    Question: there is way to add position(start:end) to every column in the ctl file?

    Thanks in advance.
    Greetings from Argentina.

Leave a Reply

Your email address will not be published. Required fields are marked *