如何在SqlLoader中装载日期型的数据

收到别人的邮件，咨询SqlLoader装载日期型数据的方法，觉得也许是一个普遍的疑问，所以贴出来。

Q：kamus：
    斑竹你好，最给予到一件麻烦事，想请教
    请问Sql loader工具能否加载日期型数据？如何加载？(我是指oracle表中字段为date型)

A：check this:
http://oraclesvca2.oracle.com/docs/cd/B10501_01/server.920/a96652/ch06.htm#1016163

Q：控制文件：
LOAD DATA
INFILE 'd:/equip.dat'
APPEND INTO TABLE scott.test
(q1 position(01:05) char,
 q2 position(06:10) char,
 q3 position(11:29) date(19) "dd/Mon/yyyy:hh24:mi:ss")
    数据文件：
aaaaaxxxxx05/05/2005:01:01:01
bbbbbyyyyy05/06/2005:01:01:02
ccccczzzzz05/07/2005:01:01:03
    加载后的日志文件：
SQL*Loader: Release 9.2.0.1.0 - Production on 星期二 5月 31 21:19:43 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

控制文件: d:/equip.ctl
数据文件: d:/equip.dat
错误文件: d:/equip.bad
废弃文件: 未作指定
:
(可废弃所有记录)

加载数: ALL
跳过数: 0
允许的错误: 50
绑定数组: 64 行，最大 256000 字节
继续:    未作指定
所用路径:       常规

表SCOTT.TEST
已加载从每个逻辑记录
插入选项对此表APPEND生效

   列名                        位置      长度  中止 包装数据类型
------------------------------ ---------- ----- ---- ---- ---------------------
Q1                                    1:5     5           CHARACTER           
Q2                                   6:10     5           CHARACTER           
Q3                                  11:29    19           DATE dd/Mon/yyyy:hh24:mi:ss

记录 1: 被拒绝 - 表 SCOTT.TEST 的列 Q3 出现错误。
ORA-01843: 无效的月份

记录 2: 被拒绝 - 表 SCOTT.TEST 的列 Q3 出现错误。
ORA-01843: 无效的月份

记录 3: 被拒绝 - 表 SCOTT.TEST 的列 Q3 出现错误。
ORA-01843: 无效的月份

表SCOTT.TEST:
0 行 加载成功
由于数据错误, 3 行 没有加载。
由于所有 WHEN 子句失败, 0 行 没有加载。
由于所有字段都为空的, 0 行 没有加载。

为结合数组分配的空间:     2432字节（64行）
读取   缓冲区字节数: 1048576

跳过的逻辑记录总数:        0<script type="text/javascript"> &amp;lt;!-- D(["mb","&amp;lt;br&amp;gt;读取的逻辑记录总数:        /r/n3&amp;lt;br&amp;gt;拒绝的逻辑记录总数:        /r/n3&amp;lt;br&amp;gt;废弃的逻辑记录总数:        0&amp;lt;/div&amp;gt;/r/n&amp;lt;div&amp;gt;从星期二 5月  31 21:19:43 2005开始运行&amp;lt;br&amp;gt;在星期二 5月  31 21:19:46 /r/n2005处运行结束&amp;lt;/div&amp;gt;/r/n&amp;lt;div&amp;gt;经过时间为: 00: 00: 03.72&amp;lt;br&amp;gt;CPU 时间为: 00: 00: 00.13&amp;lt;/div&amp;gt;/r/n&amp;lt;div&amp;gt;还是没有成功，请指点问题到底出在哪？能否写一份正确的控制文件给&amp;lt;WBR&amp;gt;我， 谢谢！&amp;lt;/div&amp;gt;&amp;lt;/font&amp;gt;",1] ); //--&amp;gt; </script>
读取的逻辑记录总数:        3
拒绝的逻辑记录总数:        3
废弃的逻辑记录总数:        0

从星期二 5月  31 21:19:43 2005开始运行
在星期二 5月  31 21:19:46 2005处运行结束

经过时间为: 00: 00: 03.72
CPU 时间为: 00: 00: 00.13

还是没有成功，请指点问题到底出在哪？能否写一份正确的控制文件给我， 谢谢！

A：

要确保sqlloader时候的环境变量NLS_DATE_FORMAT跟你指定的"dd/Mon/yyyy:hh24:mi:ss"一样。否则会报日期格式不正确的错误。

首先设置操作系统的环境变量NLS_LANG为AMERICAN_AMERICA.ZHS16GBK，UNIX系统在profile中设置，Windows系统在注册表中设置。

然后，编写测试用的数据文件，t.txt
aaaaa,xxxxx,2005-01-01 1:00:00
bbbbb,yyyyy,2005-03-01 12:00:00
ccccc,zzzzz,2005-12-01 18:00:00

创建测试表：
create table T_LOAD
(
  A VARCHAR2(10),
  B VARCHAR2(10),
  C DATE
);

编写sqlldr使用的控制文件
load data
infile 't.txt'
badfile 't.bad'
append into table t_load
fields terminated by ','
trailing nullcols
(
    a,
    b,
    c date "yyyy-mm-dd hh24:mi:ss"
)

开始做sqlldr
sqlldr control=t.ctl

装载结束以后，查看log文件

SQL*Loader: Release 9.2.0.6.0 - Production on Mon Jun 6 02:28:39 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Control File:   t.ctl
Data File:      t.txt
  Bad File:     t.bad
  Discard File:  none specified
 
 (Allow all discards)

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 T_LOAD, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
A                                   FIRST     *   ,       CHARACTER           
B                                    NEXT     *   ,       CHARACTER           
C                                    NEXT     *   ,       DATE yyyy-mm-dd hh24:mi:ss


Table T_LOAD:
  3 Rows successfully loaded.
  0 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.


Space allocated for bind array:                  49536 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             3
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Mon Jun 06 02:28:39 2005
Run ended on Mon Jun 06 02:28:41 2005

Elapsed time was:     00:00:01.38
CPU time was:         00:00:00.04

检查表：
SQL> select * from t_load;

A          B          C
---------- ---------- --------------------
aaaaa      xxxxx      2005-1-1 1:00:00
bbbbb      yyyyy      2005-3-1 12:00:00
ccccc      zzzzz      2005-12-1 18:00:00