您好,登錄后才能下訂單哦!
oracle導(dǎo)入文件時,日期格式問題
load data
infile '/iot/opthb/TIBS_HOME/xcx/test/loadfile.txt'
truncate into table zhicai_record_tmp
fields terminated by ' '
trailing nullcols(src_file_name,create_date,state_date,des_file_name)
用這個模板導(dǎo)入數(shù)據(jù)時
loadfile.txt:
JSWLWYFCG_-_01111453.20170502_-_2357+0800.ctc.dat 20170502235916 20170503000027 T_IOT_DDR_20170502.025.144.055
JSWLWYFCG_-_01111454.20170502_-_2357+0800.ctc.dat 20170502235917 20170503000027 T_IOT_DDR_20170502.025.144.056
JSWLWYFCG_-_01111455.20170502_-_2357+0800.ctc.dat 20170502235917 20170503000042 T_IOT_DDR_20170502.025.144.057
JSWLWYFCG_-_01111456.20170502_-_2357+0800.ctc.dat 20170502235917 20170503000043 T_IOT_DDR_20170502.025.144.058
JSWLWYFCG_-_01111457.20170502_-_2358+0800.ctc.dat 20170502235918 20170503000058 T_IOT_DDR_20170502.025.144.059
JSWLWYFCG_-_01111458.20170502_-_2358+0800.ctc.dat 20170503000331 20170503000347 T_IOT_DDR_20170502.025.144.075
JSWLWYFCG_-_01111459.20170502_-_2358+0800.ctc.dat 20170503000332 20170503000347 T_IOT_DDR_20170502.025.144.076
JSWLWYFCG_-_01111460.20170502_-_2359+0800.ctc.dat 20170503000332 20170503000402 T_IOT_DDR_20170502.025.144.077
JSWLWYFCG_-_01111461.20170502_-_2359+0800.ctc.dat 20170503000333 20170503000402 T_IOT_DDR_20170502.025.144.078
JSWLWYFCG_-_01111462.20170502_-_2359+0800.ctc.dat 20170503000333 20170503000416 T_IOT_DDR_20170502.025.144.079
會出現(xiàn)
Record 9: Rejected - Error on table ZHICAI_RECORD_TMP, column CREATE_DATE.
ORA-01861: literal does not match format string
這種錯誤,主要是因為日期格式不匹配,這時要對導(dǎo)入的數(shù)據(jù)進(jìn)行格式化處理
load data
infile '/iot/opthb/TIBS_HOME/xcx/test/loadfile.txt'
append into table zhicai_record_tmp
fields terminated by ' '
trailing nullcols(src_file_name,
create_date "to_date(:create_date,'yyyymmddhh34miss')",
state_date "to_date(:create_date,'yyyymmddhh34miss')",
des_file_name)
用這個模板導(dǎo)入數(shù)據(jù)時,便不會出現(xiàn)以上報錯
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。