Shulou(Shulou.com)06/01 Report--

Date format problem when oracle imports files

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)

When importing data with this template

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

Will show up.

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

ORA-01861: literal does not match format string

This kind of error is mainly due to the mismatch of date format, so the imported data should be formatted.

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)

When importing data with this template, the above error will not occur.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.