oracle导入文件时,日期格式问题

linghaiyan

　　oracle导入文件时,日期格式问题
　　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)
　　用这个模板导入数据时
　　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
　　会出现
　　Record 9: Rejected - Error on table ZHICAI_RECORD_TMP, column CREATE_DATE.
　　ORA-01861: literal does not match format string
　　这种错误,主要是因为日期格式不匹配,这时要对导入的数据进行格式化处理
　　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,'yyyymmddhh24miss')",
　　state_date "to_date(:create_date,'yyyymmddhh24miss')",
　　des_file_name)
　　用这个模板导入数据时,便不会出现以上报错