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

有这么一个数据，按照一分钟统计数据的查询数，字段内容：

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 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)

这样导入就完全正常了。