SCRIPT TO GENERATE SQL*LOADER CONTROL FILE

最新推荐文章于 2025-09-11 20:53:34 发布

weixin_30872671

最新推荐文章于 2025-09-11 20:53:34 发布

阅读量76

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/macleanoracle/archive/2013/03/19/2967198.html

本文介绍了一个用于生成SQL*Loader控制文件的脚本，该脚本能够根据已存在的数据库表自动创建加载控制文件，适用于有DDL语句但缺少对应控制文件的情况。

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)
)

转载于:https://www.cnblogs.com/macleanoracle/archive/2013/03/19/2967198.html