sqlldr导入含‘null’字符日期类型数据的方法:
在sqlldr导入一份数据清单时,数据提供方提供的一份数据,导入时,一个日期字段有错误提示:ORA-01841: (full) year must be between -4713 and +9999, and not be 0,原来导入控制文件如下:
LOAd DATA
CHARACTERSET AL32UTF8
truncate INTO TABLE c_base_d
FIELDS TERMINATED BY ‘|’
optionally enclosed by ‘"’
TRAILING NULLCOLS
(
city,
phone,
device_code,
ctei,
mac,
fwver,
user_bind_status,
create_time Date ‘yyyy-mm-dd:hh24:mi:ss’
)
日记提示在导入create_time 字段时遇到非日期类型数据,查看数据文件,发现日期字段的空值记录出现了“null”字符,显然字符不能转换成时间,导致数据转换失败,查找ctl控制文件相关资料,可以这样解决,导入控制文件修改如下:
LOAd DATA
CHARACTERSET AL32UTF8
truncate INTO TABLE c_base_d
FIELDS TERMINATED BY ‘|’
optionally enclosed by ‘"’
TRAILING NULLCOLS
(
city,
phone,
device_code,
ctei,
mac,
fwver,
user_bind_status,
create_time "case when :create_time <> ‘null’ then to_date(:create_time,‘yyyy-mm-dd:hh24:mi:ss’) end "
)
数据导入成功,查看日记无错误记录行,问题解决!