--oracle语句写ctl脚本
--文件路径 /home/hdeast/east_share/input_east/dataFile/2017-03-31/ts_wyhl_zfywdjb20170401.unl
----------------------------start------------------------------------
select 'LOAD DATA'||chr (10)||
'CHARACTERSET ZHS16GBK'||chr (10)||
'INFILE '||'''/home/hdeast/east_share/input_east/dataFile/2017-03-31/ts_wyhl_zfywdjb20170401.unl'''||chr (10)||
'BADFILE '||'''/home/hdeast/east_share/input_east/dataFile/2017-03-31/ts_wyhl_zfywdjb.bad'''||chr (10)||
'TRUNCATE INTO TABLE '|| TABLE_NAME||chr (10)||
'FIELDS TERMINATED BY ''|'''||chr (10)||
'TRAILING NULLCOLS'||chr (10)||'('
from user_tables where TABLE_NAME= upper( 'T_MODEL_DGKH_0') union all
select decode (rownum, 1, ' ', ' , ')||
rpad (column_name, 33, ' ')||
decode (data_type,
'VARCHAR2', --'CHAR('|| DATA_LENGTH ||') NULLIF ('||column_name||'=BLANKS)',
'"trim(:'||column_name||')"',
'CHAR', 'CHAR('|| DATA_LENGTH ||') NULLIF ('||column_name||'=BLANKS)',
'FLOAT', 'DECIMAL EXTERNAL NULLIF('||column_name||'=BLANKS)',
'TIMESTAMP(6)','"LOCALTIMESTAMP"',
/* '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)')),*/
-- 'NUMBER', '"to_number(:'||column_name||'),"'
'NUMBER', '"trim(:'||column_name||')"',
-- 'DATE', 'DATE "yyyy-MM-dd HH24:MI:SS" NULLIF ('||column_name||'=BLANKS)',
'DATE', '"to_date(:'||column_name||','||'''yyyy/mm/dd'''||')" ',
null)
from (select * from user_tab_columns order by column_id)
where table_name = upper( 'T_MODEL_DGKH_0') union all
select ')'
from sys.dual ;
-------------------------------------------end--------------------------------------------------------
sqlldr语句
sqlldr zhsb/zhsb@zhdb control='/home/zhsb/ctl/O_E_ZHSB_GRYHZH.ctl' log='/home/zhsb/zhsb.log' bad='/home/zhsb/zhsb.bad' direct=true rows=5000
用命令窗口执行即可;