自动生成sqlloader的控制文件

Love The Way You Lie 2022-05-30 03:19 171阅读 0赞

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

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