创建表:
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 2011Copyright (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: ConventionalTable 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:SSRecord 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 presentTable 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: 1048576Total logical records skipped: 1
Total logical records read: 5
Total logical records rejected: 4
Total logical records discarded: 1Run began on Fri Dec 30 07:23:05 2011
Run ended on Fri Dec 30 07:23:06 2011Elapsed 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
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 2011Copyright (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: ConventionalTable 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:SSRecord 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 presentTable 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: 1048576Total logical records skipped: 1
Total logical records read: 5
Total logical records rejected: 4
Total logical records discarded: 1Run began on Fri Dec 30 07:23:05 2011
Run ended on Fri Dec 30 07:23:06 2011Elapsed 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
把
optionally enclosed by "" --表中的每列数据用""框起来,当数据中有' 时
改为
optionally enclosed by '\''
因为在你的原始数据中,姓名字段中有单引号出现。其实本来写成enclosed by '''就可以,但是这里还需要在中间的单引号前面加一个转义字符\