oracle導(dǎo)入文件時(shí),日期格式問題

南山網(wǎng)站制作公司哪家好，找創(chuàng)新互聯(lián)公司！從網(wǎng)頁(yè)設(shè)計(jì)、網(wǎng)站建設(shè)、微信開發(fā)、APP開發(fā)、成都響應(yīng)式網(wǎng)站建設(shè)等網(wǎng)站項(xiàng)目制作，到程序開發(fā)，運(yùn)營(yíng)維護(hù)。創(chuàng)新互聯(lián)公司于2013年開始到現(xiàn)在10年的時(shí)間，我們擁有了豐富的建站經(jīng)驗(yàn)和運(yùn)維經(jīng)驗(yàn)，來(lái)保證我們的工作的順利進(jìn)行。專注于網(wǎng)站建設(shè)就選創(chuàng)新互聯(lián)公司。

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)

用這個(gè)模板導(dǎo)入數(shù)據(jù)時(shí)

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

會(huì)出現(xiàn)

Record 9: Rejected - Error on table ZHICAI_RECORD_TMP, column CREATE_DATE.

ORA-01861: literal does not match format string

這種錯(cuò)誤,主要是因?yàn)槿掌诟袷讲黄ヅ?這時(shí)要對(duì)導(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)

用這個(gè)模板導(dǎo)入數(shù)據(jù)時(shí),便不會(huì)出現(xiàn)以上報(bào)錯(cuò)