1.创建ctl sql文件 &参数,&1为ctl文件名,&2为插入方 式 此处选择 truncate 为先清空再插入 append为追加
&3为分隔符 没有用到写死的‘,’,&4为表名 ,通过执行下面的bat 将参数带入bat 进行执行语句。
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)|| 'CHARACTERSET UTF8 '||
'INFILE '''||'&1'||'.txt'''||
'&2 into table '||table_name||chr (10)||
'FIELDS TERMINATED BY "," '|| chr (10)||
'TRAILING NULLCOLS'||chr (10)||'('
from user_tables
where table_name = upper ('&4');
select decode (rownum, 1, ' ', ' , ')||
rpad (column_name, 33, ' ')||
decode (data_type,
'VARCHAR2', 'CHAR('||RTRIM(TO_CHAR(DATA_LENGTH+4)) ||') NULLIF ('||column_name||'=BLANKS)'||' "trim(:'||column_name||')"',
'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" NULLIF ('||column_name||'=BLANKS)',
null)
from
(select * from user_tab_columns
where table_name = upper ('&4')
order by column_id ) t;
select ')'
from sys.dual;
spool off
quit
2.写脚本 执行 ctl文件
sqlplus test/123456@localhost/orcl @D:\create_control_extend.sql txt文件名 truncate "," tablename
sqlplus test/123456@localhost/orcl @D:\create_control_extend.sql txt文件名2 truncate "," tablename2
3.写导入脚本 input.bat
sqlldr userid=test/123456 control=ctl1.ctl log=log1.out discard=discard.txt errors=1000000
sqlldr userid=test/123456 control=ctl2.ctl log=log2.out discard=discard.txt errors=1000000