使用sqlldr导入日期格式字段的问题

最新推荐文章于 2021-05-11 04:49:12 发布

congchuang7723

最新推荐文章于 2021-05-11 04:49:12 发布

阅读量1.6k

点赞数

文章标签：数据库 awk

有这么一个数据，按照一分钟统计数据的查询数，字段内容：
stat_date date, stat_num number(10)
stat_date表示统计日期，stat_num表示统计数目
数据如下：
2009-03-04 15:40:00, 4454
2009-03-04 15:41:00, 3360
2009-03-04 15:42:00, 7543
现要将这些数据导入到数据库的w_ldap_stat表中，我首先想到了用
awk将数据组装成SQL语句。即每条记录一个insert语句插入到数据库
中。以前也做过类似的脚本。但是感觉这样比较繁琐，而且也没有必
要，因为Oracle已经为我们提供了一个现成的工具：sqlldr。
使用这个工具，我们就不用将每行记录都组装成insert语句来插入了。
使用sqlldr加载数据，首先需要创建一个控制文件，用于对要导入的数
据进行针对性的配置：
------------------------------ldapload.ctl----------------------------
load data
infile '20090417.dat' --指定要导入的文件名
--insert                           --插入数据，要求源数据表为空
append                       --扩展数据，在原表基础上增加数据
--replace                        --替换数据，删除原有数据，再插入新数据
into table w_ldap_stat --指定表名
fields terminated by ','   --指定字段分隔符
(stat_date, stat_num)   --指定导入对应的字段

使用sqlldr进行导入：
sqlldr test/abc123@orcl control=ldapload.ctl
结果导入情况如下：
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table W_LDAP_STAT, loaded from every logical record.
Insert option in effect for this table: APPEND

   Column Name                  Position   Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
STAT_DATE                           FIRST     *   , O(") CHARACTER
STAT_NUM                             NEXT     *   , O(") CHARACTER

Data File 30.dat -

Record 1: Rejected - Error on table W_LDAP_STAT, column STAT_NUM.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 2: Rejected - Error on table W_LDAP_STAT, column STAT_NUM.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 3: Rejected - Error on table W_LDAP_STAT, column STAT_NUM.
Column not found before end of logical record (use TRAILING NULLCOLS)
...
Record 7: Rejected - Error on table W_LDAP_STAT, column STAT_DATE.
ORA-01861: literal does not match format string

Record 8: Rejected - Error on table W_LDAP_STAT, column STAT_DATE.
ORA-01861: literal does not match format string
MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.
....
Table W_LDAP_STAT:
0 Rows successfully loaded.
51 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
从日志中可以看到，出现了两个错误。
一个是stat_num字段有插入空数据的情况，这个的确是不需要的数据，可以排除。
一个是stat_date字段出现了ORA-01861错误，日期格式不匹配。这个是为什么呢？
难道是在源数据中还要转成日期格式？如下：
to_date('2009-03-04 15:40:00','yyyy-mm-dd hh24:mi:ss'), 4454
to_date('2009-03-04 15:41:00','yyyy-mm-dd hh24:mi:ss'), 3360
to_date('2009-03-04 15:42:00','yyyy-mm-dd hh24:mi:ss'), 7543
再次导入出现如下错误：
Record 1: Rejected - Error on table W_LDAP_STAT, column STAT_DATE.
ORA-01858: a non-numeric character was found where a numeric was expected

Record 2: Rejected - Error on table W_LDAP_STAT, column STAT_DATE.
ORA-01858: a non-numeric character was found where a numeric was expected

Record 3: Rejected - Error on table W_LDAP_STAT, column STAT_DATE.
ORA-01858: a non-numeric character was found where a numeric was expected
奇怪了，是什么原因呢？难道是因为空格将日期和时间分开的缘故？
于是尝试增加
optionally enclosed by '"'
用以把对应的字段给引起来，表示这是一个字段的内容
"2009-03-04 15:40:00", 4454
"2009-03-04 15:41:00", 3360
"2009-03-04 15:42:00", 7543
但导入还是失败。难道是我比较笨，不能理解sqlldr的功能？
找找网上看，看有没有跟我一样笨的。结果不搜不知道一搜一大堆。
看来这个不是个别问题啊，是普遍存在的，老外也有不少遇到的。
最后看到一个解决方法，就是在控制文件中，对导入的日期的字段进行格式化：
stat_date timestamp 'yyyy-mm-dd hh24:mi:ss'
即如下：
--------------------------------ldapload.ctl-------------------------------
load data
infile 'test.dat'
--infile '29.dat'
--insert
append
into table w_ldap_stat
fields terminated by ','
optionally enclosed by '"'
(stat_date timestamp "yyyy-mm-dd hh24:mi:ss", stat_num)

这样导入就完全正常了。