描述
oracle 10g 042课程中关于sqlldr使用的例子未写,所以想着自己设计一个实验使用sqlldr将数据从一个平面文件导入到数据库中.本以为是很简单的一个实验,结果却因为csv文件的原因致使导入一直不成功.百思之下做了一个在每一行行尾添加一个分隔逗号,结果却导入成功了,不明所以,这里记录之.
环境
OS
$cat /etc/redhat-releaseRed Hat Enterprise Linux Server release 5.6 (Tikanga)
$uname -a
Linux stu00 2.6.18-238.el5 #1 SMP Tue Jan 4 15:24:05 EST 2011 i686 i686 i386 GNU/Linux
DB
sqlplus / as sysdbaSQL> set lines 150
COL PRODUCT FORMAT A55
COL VERSION FORMAT A15
COL STATUS FORMAT A15
SELECT * FROM PRODUCT_COMPONENT_VERSION;SQL> SQL> SQL> SQL>
PRODUCT VERSION STATUS
------------------------------------------------------- --------------- ---------------
NLSRTL 10.2.0.4.0 Production
Oracle Database 10g Enterprise Edition 10.2.0.4.0 Prod
PL/SQL 10.2.0.4.0 Production
TNS for Linux: 10.2.0.4.0 Production
SQL>
OTHER
sqldeveloper
关于
--
Oracle SQL Developer 3.1.07
版本 3.1.07
工作版本 MAIN-07.42
版权所有 (c) 2005, 2011 Oracle。保留所有权利。
IDE Version: 11.1.1.4.37.59.48
Product ID: oracle.sqldeveloper
Product Version: 11.2.0.07.42
版本
--
组件 版本
== ==
版本化支持 3.1.07.42
Oracle IDE 3.1.07.42
Java(TM) 平台 1.6.0_11
结构大体设计
1.利用sqldeveloper的导出功能将scott.emp表数据导出成csv文件类型.2.根据1步骤导出的csv文件设计hr.emp表结构
3.编写sqlldr控制文件
4.使用sqlldr工具利用控制文件从数据源文件(csv)加载数据到数据库
详细步骤
1.sqldeveloper将scott.emp表数据导出成csv文件这里不做操作说明.如下列出csv文件的内容.a.执行的sql语句
select empno,ename,job,mgr,to_char(hiredate,'yyyy-mm-dd') hiredate,sal,comm,deptno from scott.emp;
b.cat文件的内容
$cat emp_date_easy.csv
7369,"SMITH","CLERK",7902,"1980-12-17",800,,20
7499,"ALLEN","SALESMAN",7698,"1981-02-20",1600,300,30
7521,"WARD","SALESMAN",7698,"1981-02-22",1250,500,30
7566,"JONES","MANAGER",7839,"1981-04-02",2975,,20
7654,"MARTIN","SALESMAN",7698,"1981-09-28",1250,1400,30
7698,"BLAKE","MANAGER",7839,"1981-05-01",2850,,30
7782,"CLARK","MANAGER",7839,"1981-06-09",2450,,10
7788,"SCOTT","ANALYST",7566,"1987-04-19",3000,,20
7839,"KING","PRESIDENT",,"1981-11-17",5000,,10
7844,"TURNER","SALESMAN",7698,"1981-09-08",1500,0,30
7876,"ADAMS","CLERK",7788,"1987-05-23",1100,,20
7900,"JAMES","CLERK",7698,"1981-12-03",950,,30
7902,"FORD","ANALYST",7566,"1981-12-03",3000,,20
7934,"MILLER","CLERK",7782,"1982-01-23",1300,,10
2.根据内容自己定义创建将要导入的目的表hr.emp .创建命令如下:
CREATE TABLE "HR"."EMP"
(
"EMPNO" NUMBER,
"ENAME" VARCHAR2(20 BYTE),
"JOB" VARCHAR2(20 BYTE),
"MGR" NUMBER,
"HIREDATE" DATE,
"SAL" NUMBER,
"COMM" NUMBER,
"DEPTNO" NUMBER
);
3.编写sqlldr控制文件内容,如下:
$cat sqlldr_control_emp_date_easy.txt
LOAD DATA
INFILE 'emp_date_easy.csv'
BADFILE 'emp_120609.csv_bad.txt'
DISCARDFILE 'emp_120609_dis.txt'
truncate
INTO TABLE emp
fields terminated by "," optionally enclosed by '"'
TRAILING NULLCOLS
(
empno integer external,
ename ,
job ,
mgr integer external,
hiredate DATE "YYYY-MM-DD",
sal integer external ,
comm integer external ,
deptno integer external
)
$
4. 使用sqlldr命令执行导入操作(问题发生)
a. 执行导入命令
$sqlldr hr/hr control=sqlldr_control_emp_date_easy.txt
SQL*Loader: Release 10.2.0.4.0 - Production on Sat Jun 9 02:01:59 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Commit point reached - logical record count 14
b.查看sqlldr日志
$cat sqlldr_control_emp_date_easy.log
SQL*Loader: Release 10.2.0.4.0 - Production on Sat Jun 9 02:01:59 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Control File: sqlldr_control_emp_date_easy.txt
Data File: emp_date_easy.csv
Bad File: emp_120609.csv_bad.txt
Discard File: emp_120609_dis.txt
(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 EMP, 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
------------------------------ ---------- ----- ---- ---- ---------------------
EMPNO FIRST * , O(") CHARACTER
ENAME NEXT * , O(") CHARACTER
JOB NEXT * , O(") CHARACTER
MGR NEXT * , O(") CHARACTER
HIREDATE NEXT * , O(") DATE YYYY-MM-DD
SAL NEXT * , O(") CHARACTER
COMM NEXT * , O(") CHARACTER
DEPTNO NEXT * , O(") CHARACTER
Record 1: Rejected - Error on table EMP, column DEPTNO.
ORA-01722: invalid number
Record 2: Rejected - Error on table EMP, column DEPTNO.
ORA-01722: invalid number
Record 3: Rejected - Error on table EMP, column DEPTNO.
ORA-01722: invalid number
Record 4: Rejected - Error on table EMP, column DEPTNO.
ORA-01722: invalid number
Record 5: Rejected - Error on table EMP, column DEPTNO.
ORA-01722: invalid number
Record 6: Rejected - Error on table EMP, column DEPTNO.
ORA-01722: invalid number
Record 7: Rejected - Error on table EMP, column DEPTNO.
ORA-01722: invalid number
Record 8: Rejected - Error on table EMP, column DEPTNO.
ORA-01722: invalid number
Record 9: Rejected - Error on table EMP, column DEPTNO.
ORA-01722: invalid number
Record 10: Rejected - Error on table EMP, column DEPTNO.
ORA-01722: invalid number
Record 11: Rejected - Error on table EMP, column DEPTNO.
ORA-01722: invalid number
Record 12: Rejected - Error on table EMP, column DEPTNO.
ORA-01722: invalid number
Record 13: Rejected - Error on table EMP, column DEPTNO.
ORA-01722: invalid number
Record 14: Rejected - Error on table EMP, column DEPTNO.
ORA-01722: invalid number
Table EMP:
0 Rows successfully loaded.
14 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: 132096 bytes(64 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 14
Total logical records rejected: 14
Total logical records discarded: 0
Run began on Sat Jun 09 02:01:59 2012
Run ended on Sat Jun 09 02:02:01 2012
Elapsed time was: 00:00:01.59
CPU time was: 00:00:00.03
由于hr.emp相应的dept列是number类型,按以往的经验来说不应该是控制文件的问题.但也不能太相信经验了,于是各种的修改sqlldr的控制文件呀.最后没有办法,怀疑到是不是csv源文件的原因引起的.遂在csv文件每一行最后一列添加了",". 结果如下:
$cat !$
cat emp_date_easy.csv
7369,"SMITH","CLERK",7902,"1980-12-17",800,,20,
7499,"ALLEN","SALESMAN",7698,"1981-02-20",1600,300,30,
7521,"WARD","SALESMAN",7698,"1981-02-22",1250,500,30,
7566,"JONES","MANAGER",7839,"1981-04-02",2975,,20,
7654,"MARTIN","SALESMAN",7698,"1981-09-28",1250,1400,30,
7698,"BLAKE","MANAGER",7839,"1981-05-01",2850,,30,
7782,"CLARK","MANAGER",7839,"1981-06-09",2450,,10,
7788,"SCOTT","ANALYST",7566,"1987-04-19",3000,,20,
7839,"KING","PRESIDENT",,"1981-11-17",5000,,10,
7844,"TURNER","SALESMAN",7698,"1981-09-08",1500,0,30,
7876,"ADAMS","CLERK",7788,"1987-05-23",1100,,20,
7900,"JAMES","CLERK",7698,"1981-12-03",950,,30,
7902,"FORD","ANALYST",7566,"1981-12-03",3000,,20,
7934,"MILLER","CLERK",7782,"1982-01-23",1300,,10,
c.接着执行sqlldr命令
$sqlldr hr/hr control=sqlldr_control_emp_date_easy.txt
SQL*Loader: Release 10.2.0.4.0 - Production on Sat Jun 9 02:07:00 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Commit point reached - logical record count 14
d.查看新验证方式的日志信息(成功)
$cat sqlldr_control_emp_date_easy.log
SQL*Loader: Release 10.2.0.4.0 - Production on Sat Jun 9 02:07:00 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Control File: sqlldr_control_emp_date_easy.txt
Data File: emp_date_easy.csv
Bad File: emp_120609.csv_bad.txt
Discard File: emp_120609_dis.txt
(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 EMP, 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
------------------------------ ---------- ----- ---- ---- ---------------------
EMPNO FIRST * , O(") CHARACTER
ENAME NEXT * , O(") CHARACTER
JOB NEXT * , O(") CHARACTER
MGR NEXT * , O(") CHARACTER
HIREDATE NEXT * , O(") DATE YYYY-MM-DD
SAL NEXT * , O(") CHARACTER
COMM NEXT * , O(") CHARACTER
DEPTNO NEXT * , O(") CHARACTER
Table EMP:
14 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: 132096 bytes(64 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 14
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Sat Jun 09 02:07:00 2012
Run ended on Sat Jun 09 02:07:00 2012
Elapsed time was: 00:00:00.38
CPU time was: 00:00:00.03
$
个人总结
sqlldr源数据文件(csv格式)不理解为什么要在每行行尾添加","才能成功导入.此处以记之.
另csv数据源文件中的日期格式是 yyyy-mm-dd. 如果日期格式显示的结果类似如下(即日期显示中包含中文)应该如何写sqlldr的控制文件?
7369,SMITH,CLERK,7902,17-12月-80,800,,20
7499,ALLEN,SALESMAN,7698,20-2月 -81,1600,300,30
7521,WARD,SALESMAN,7698,22-2月 -81,1250,500,30
7566,JONES,MANAGER,7839,02-4月 -81,2975,,20
7654,MARTIN,SALESMAN,7698,28-9月 -81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-5月 -81,2850,,30
7782,CLARK,MANAGER,7839,09-6月 -81,2450,,10
7788,SCOTT,ANALYST,7566,19-4月 -87,3000,,20
7839,KING,PRESIDENT,,17-11月-81,5000,,10
7844,TURNER,SALESMAN,7698,08-9月 -81,1500,0,30
7876,ADAMS,CLERK,7788,23-5月 -87,1100,,20
7900,JAMES,CLERK,7698,03-12月-81,950,,30
7902,FORD,ANALYST,7566,03-12月-81,3000,,20
7934,MILLER,CLERK,7782,23-1月 -82,1300,,10
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11780477/viewspace-732367/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/11780477/viewspace-732367/