自动生成sqlloader的控制文件

sqlloader 运行过程需要控制文件，一下sql 脚本实现了自动生成控制文件

脚本如下（假如命名为ctl.sql）：

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 'OPTIONS (DIRECT=true)'||chr (10)||
       'LOAD DATA'||chr (10)||
       'INFILE '''||lower (table_name)||'.dat'''||chr (10)||
       '&2 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 "YYYY-MM-DD HH24:MI:SS"  NULLIF ('||column_name||'=BLANKS)', null)
from user_tab_columns
where table_name=upper('&1')
order by column_id;
select ')'
from sys.dual;
spool off

然后你就登陆到数据库中执行该sql 脚本 需要给两个参数第一就是 表名 第二就是 模式 （比如 insert, append, replace, truncate）

SQL> start ctl.sql qq APPEND
LOAD DATA
INFILE 'qq.dat'
APPEND INTO TABLE QQ
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(

   ID                   INTEGER EXTERNAL NULLIF (ID=BLANKS)
 , QQ_NUMBER                CHAR NULLIF (QQ_NUMBER=BLANKS)
 , QQ_STATUS                CHAR NULLIF (QQ_STATUS=BLANKS)
 , QQ_NAME              CHAR NULLIF (QQ_NAME=BLANKS)
 , QQ_AGE               INTEGER EXTERNAL NULLIF (QQ_AGE=BLANKS)
 , QQ_EMAIL             CHAR NULLIF (QQ_EMAIL=BLANKS)
 , QQ_ZONE              CHAR NULLIF (QQ_ZONE=BLANKS)
 , IS_VIP               CHAR NULLIF (IS_VIP=BLANKS)
 , ADDRESS              CHAR NULLIF (ADDRESS=BLANKS)
 , REGISTER_DATE            DATE "YYYY-MM-DD HH24:MI:SS"  NULLIF (REGISTER_DATE=BLANKS)
 , LAST_LOGIN_TIME          DATE "YYYY-MM-DD HH24:MI:SS"  NULLIF (LAST_LOGIN_TIME=BLANKS)
 , LAST_LOGIN_ADDR          CHAR NULLIF (LAST_LOGIN_ADDR=BLANKS)
 , LAST_LOGIN_TERMINAL          CHAR NULLIF (LAST_LOGIN_TERMINAL=BLANKS)
)

运行完毕之后就会在当前路径下面生成了控制文件，可以直接拿去用了