Question

sqlldr倒入时间的问题

ctl文件如下：
Load data
Infile 'USER_CLIENT_INFO_tj.txt'
insert Into table USER_CLIENT_INFO_tj
fields terminated by ',' OPTIONALLY ENCLOSED BY '"'
trailing nullcols
(USER_ID,CHANNEL_ID,CLIENT_ID,SEX,TIME)
------log------

列名 位置 长度 中止 包装数据类型
------------------------------ ---------- ----- ---- ---- ---------------------
USER_ID FIRST * , O (") CHARACTER
CHANNEL_ID NEXT * , O (") CHARACTER
CLIENT_ID NEXT * , O (") CHARACTER
SEX NEXT * , O (") CHARACTER
TIME NEXT * , O (") CHARACTER

记录 1: 被拒绝 - 表 USER_CLIENT_INFO_TJ 的列 TIME 出现错误。
ORA-01861: 文字与格式字符串不匹配

----------文本---------
48410293,006,13,m,2011-05-14 14:01:19.0
48425627,008,13,f,2011-05-14 18:42:42.0
48434851,008,13,f,2011-05-14 21:24:42.0
我知道好像是导入时间的格式问题，但是不知道该如何能把时间通过sqlldr导入到Orcle中呢？求解啊，万分感谢

Answer 1

Load data
Infile 'USER_CLIENT_INFO_tj.txt'
insert Into table USER_CLIENT_INFO_tj
fields terminated by ',' OPTIONALLY ENCLOSED BY '"'
trailing nullcols
(USER_ID,CHANNEL_ID,CLIENT_ID,SEX,TIME "to_date(:time,'YYYY-MM-DD HH24:MI:SS)")

追问

.ctl文件内容--------------------
Load data
Infile 'USER_CLIENT_INFO_tj.txt'
insert Into table USER_CLIENT_INFO_tj
fields terminated by ',' OPTIONALLY ENCLOSED BY '"'
trailing nullcols
(USER_ID,CHANNEL_ID,CLIENT_ID,SEX,TIME TimeStamp 'yyyy-mm-dd hh24:mi:ss.ff1')
------------------------------
这个是正确的，起哦已经自己解决了