描述

oracle 10g 042课程中关于sqlldr使用的例子未写,所以想着自己设计一个实验使用sqlldr将数据从一个平面文件导入到数据库中.本以为是很简单的一个实验,结果却因为csv文件的原因致使导入一直不成功.百思之下做了一个在每一行行尾添加一个分隔逗号,结果却导入成功了,不明所以,这里记录之.

环境

OS

$cat /etc/redhat-release
Red 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 sysdba

SQL> 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博客

sqlldr简单实验-基于sqldeveloper与scott.emp表

描述

环境

OS

DB

OTHER

sqldeveloper

结构大体设计

详细步骤

个人总结

最新文章