oracle sqlloder 出错

[jyou2008]

创建表：
create table users(user_id number, user_name varchar2(50),login_times number,last_login date);

外部文件：
SQL> select * from users;
1 Un'mi 3 2009-1-5 20:34
2 5 2008-10-15
3 隔叶黄莺 8 2009-1-2
4 Kypfos 10
5 不知秋 12008-12-23

控制文件：
options (skip=1,rows=100) --sqlloder命令显示的选项可以写在里面，skip=2 -->从第三行开始加载
-- rows=100 每一百行提交一次
load data
infile '/home/oracle/users.txt' --指定外部数据文件 可以写成多个infile，为多个外部数据文件
--这里可以指定badfile,discardfile指定坏数据和丢弃的数据文件
truncate --操作类型 把原表中的数据进行清空
into table users --要进行加载数据到的目标表
fields terminated by whitespace --数据中每列数据用空格分隔
optionally enclosed by "" --表中的每列数据用""框起来，当数据中有' 时
trailing nullcols --表中的数据没有对应值时允许为空
(
--virtual_column filler, --这是一个虚拟字段，用来跳过由pl/sql developer生成的第一个列序号
user_id decimal external, --不是character类型 请指定类型
user_name,
login_times integer external,
last_login DATE "YYYY-MM-DD HH24:MI:SS" --接受日期的格式，相当用to_date.
)

sqlldr log:

SQL*Loader: Release 11.2.0.1.0 - Production on Fri Dec 30 07:23:05 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Control File: users.ctl
Data File: /home/oracle/users.txt
Bad File: users.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 1
Errors allowed: 50
Bind array: 100 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table USERS, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
USER_ID FIRST * WHT O(X00) CHARACTER
USER_NAME NEXT * WHT O(X00) CHARACTER
LOGIN_TIMES NEXT * WHT O(X00) CHARACTER
LAST_LOGIN NEXT * WHT O(X00) DATE YYYY-MM-DD HH24:MI:SS

Record 1: Discarded - all columns null.
Record 2: Rejected - Error on table USERS, column USER_ID.
second enclosure string not present
Record 3: Rejected - Error on table USERS, column USER_ID.
second enclosure string not present
Record 4: Rejected - Error on table USERS, column USER_ID.
second enclosure string not present
Record 5: Rejected - Error on table USERS, column USER_ID.
second enclosure string not present

Table USERS:
0 Rows successfully loaded.
4 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
1 Row not loaded because all fields were null.


Space allocated for bind array: 103200 bytes(100 rows)
Read buffer bytes: 1048576

Total logical records skipped: 1
Total logical records read: 5
Total logical records rejected: 4
Total logical records discarded: 1

Run began on Fri Dec 30 07:23:05 2011
Run ended on Fri Dec 30 07:23:06 2011

Elapsed time was: 00:00:01.20
CPU time was: 00:00:00.03
======================================================================================================
报这个错误：
Record 2: Rejected - Error on table USERS, column USER_ID.
second enclosure string not present

[yixilan]

[Quote=引用楼主 jyou2008 的回复:]
second enclosure string not present
[/Quote]
你的ctl文件：
把
optionally enclosed by "" --表中的每列数据用""框起来，当数据中有' 时
改为
optionally enclosed by '\''
因为在你的原始数据中，姓名字段中有单引号出现。其实本来写成enclosed by '''就可以，但是这里还需要在中间的单引号前面加一个转义字符\