给大家拜个早年，顺便问问关于SQL*Loader的问题（很急）

Question

表结构如下：
CREATE TABLE ActionLog  (
 Id   number(18, 0)  NOT NULL ,
 Type   varchar2(20) NOT NULL ,
 Voucher   varchar2(20) NOT NULL ,
 VoucherId   number(18, 0) NOT NULL ,
 Modifier   varchar2(20) NOT NULL ,
 ModifyTime   date  NOT NULL ,
 Memo   varchar2(255) NULL 
) ;
控制文件如下：LOAD DATA
INFILE 'ActionLog.txt'
APPEND INTO TABLE ActionLog
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED '"'
TRAILING NULLCOLS
(Id,Type,Voucher,
VoucherId,Modifier,
ModifyTime date "yyyy-mm-dd hh24:mi:ss" "to_date(:ModifyTime,'yyyy-mm-dd hh24:mi:ss')",
Memo)数据如下：1,"生成","订单",205128,"张三",2002-02-04 10:35:53.877000000,""
2,"删除","订单",205128,"张三",2002-02-04 10:36:07.220000000,""
3,"生成","订单",205128,"孙冬梅",2002-02-04 11:52:41.467000000,""
4,"生成","订单",205129,"孙冬梅",2002-02-04 11:54:08.953000000,""
5,"生成","订单",205146,"孙冬梅",2002-02-04 11:56:12.890000000,""
6,"生成","订单",205164,"孙冬梅",2002-02-04 11:57:07.750000000,""
7,"生成","订单",205215,"孙冬梅",2002-02-04 12:00:19.800000000,""
8,"生成","订单",205260,"孙冬梅",2002-02-04 12:02:27.473000000,""
9,"生成","订单",205265,"唐慧岭",2002-02-04 12:02:41.117000000,""
10,"生成","订单",205282,"唐慧岭",2002-02-04 12:07:22.853000000,""
11,"生成","订单",205283,"孙冬梅",2002-02-04 12:09:04.620000000,""
12,"生成","订单",205284,"孙冬梅",2002-02-04 12:12:12.263000000,""
13,"生成","订单",205285,"孙冬梅",2002-02-04 12:13:24.983000000,""
14,"生成","订单",205286,"孙冬梅",2002-02-04 12:14:17.297000000,""
15,"生成","订单",205287,"唐慧岭",2002-02-04 12:15:10.330000000,""
16,"生成","订单",205288,"孙冬梅",2002-02-04 12:15:28,""
17,"生成","订单",205289,"孙冬梅",2002-02-04 12:19:37.770000000,""
18,"生成","订单",205290,"秦正伟",2002-02-04 12:20:56.757000000,""
19,"生成","订单",205291,"李颖舒",2002-02-04 12:23:44.647000000,""
20,"生成","订单",205292,"孙冬梅",2002-02-04 12:26:32.853000000,""
21,"生成","订单",205293,"唐慧岭",2002-02-04 12:30:23.763000000,""
22,"生成","订单",205294,"李颖舒",2002-02-04 12:31:06.497000000,""
23,"生成","订单",205295,"孙冬梅",2002-02-04 12:32:36.903000000,""
24,"生成","订单",205296,"居阳",2002-02-04 12:32:41.750000000,""

Answer 1

1、你定义的是使用“，”分割，但是在时间处：
   2002-02-04 10:35:53.877000000
   而你的时间转换使用的：yyyy-mm-dd hh24:mi:ss
   显然无法转换
2、出现中文字符出错的问题我也遇到过，一般是在截取中文是截取位置错误造成的，比如，取了半个字符等等

Answer 2

to moonlake:
     问题你指出来了，可我还是不知道该怎么解决？能不能再进一步指点指点！

Answer 3

oracle 的date时间类型只能存储到秒，所以，除非你使用字符串来存储
不然2002-02-04 10:35:53.877000000ModifyTime "to_date(substr(:ModifyTime,1,19),'yyyy-mm-dd hh24:mi:ss' )",

Answer 4

不行啊，我试了试，数据是可以倒了，但倒出来的结果：
1 Iz3I 6)5% 205128 V\P!>| 0004-2-2
2 I>3} 6)5% 205128 V\P!>| 0004-2-2
3 Iz3I 6)5% 205128 Ko6,C7 0004-2-2
4 Iz3I 6)5% 205129 Ko6,C7 0004-2-2
5 Iz3I 6)5% 205146 Ko6,C7 0004-2-2
6 Iz3I 6)5% 205164 Ko6,C7 0004-2-2
7 Iz3I 6)5% 205215 Ko6,C7 0004-2-2
8 Iz3I 6)5% 205260 Ko6,C7 0004-2-2
9 Iz3I 6)5% 205265 LF;[Ak 0004-2-2
.....中文乱码再说，可日期不对，也没有时间，郁闷

Answer 5

我说，可能是这里有问题 substr(:ModifyTime,1,19你的中间的空格是一位还是？
能否确信19位是正确的，你先做个测试看看最坏的办法： 
用一个临时表把日期数据按照字符串导进去
然后计算日期字符串长度
把该表的数据导入正式表当然你这样也可以发现你的这个日期字符串长度到底该怎么截取

Answer 6

时间问题我已经解决了，是在从sqlserver倒出来的时候将时间做了如下处理：
SUBSTRING(CONVERT(varchar(30), ModifyTime, 20), 1, 19) AS ModifyTime可中文问题还是没有解决
很感谢biti_rainy,还有moonlake

Answer 7

你的环境变量和你的数据库字符集是不是一样的？
请把 NLS_LANG设置为跟数据库的字符集一样

Answer 8

是不是在/etc/profile中设置？那我已经设过了，是不是还需要重新启动机器才行？