SQLLOADER 2 terminal by whitespace or x'09'

最新推荐文章于 2023-11-07 13:42:42 发布

cnk45946

最新推荐文章于 2023-11-07 13:42:42 发布

阅读量333

点赞数

文章标签：数据库操作系统

一描述

此档是sqlloader学习渐进的第二篇.区分全局指定FIELDS TERMINATED BY WHITESPACE 与FIELDS TERMINATED BY x'09' 的不同.

二操作环境

OS info

windows server 2003 32bit

DB info

C:\oracle\product\9.2.0\db_1\rdbms\demo>sqlplus "sys/change_on_install as sysdba"

SQL*Plus: Release 9.0.1.0.1 - Production on Sun Aug 5 13:42:35 2012

(c) Copyright 2001 Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> set lines 150
SQL> COL PRODUCT FORMAT A55
SQL> COL VERSION FORMAT A15
SQL> COL STATUS FORMAT A15
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT                                                 VERSION         STATUS
------------------------------------------------------- --------------- ---------------
NLSRTL                                                  9.0.1.1.1       Production
Oracle9i Enterprise Edition                             9.0.1.1.1       Production
PL/SQL                                                  9.0.1.1.1       Production
TNS for 32-bit Windows:                                 9.0.1.1.0       Production

SQL>

other

脚本目录:C:\sqlloader_exec\ppt_case2

三过程设计

1.准备的flat源数据.
10 Sales Virginia
20    Accounting    "Virginia,USA"
30     Consulting    Virginia

( 第一行各列间以1个空格分隔,第二行各列间以1个"TAB"分隔,第三行各列间以3个空格分隔,目的用以区分sqlloader控制文件中whitespace与16进制x'09'的不同)

2.相关文件ppt_case2.sql
rem
rem
rem

set termout off

drop table dept_p2w;
drop table dept_p2x;
create table dept_p2w (deptno number(2),dname char(20),loc char(20));
create table dept_p2x (deptno number(2),dname char(20),loc char(20));
exit;

3.相关文件ppt_case2w.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT_P2W
TRUNCATE
FIELDS TERMINATED BY WHITESPACE OPTIONALLY ENCLOSED BY '"'
-- FIELDS TERMINATED BY x'09' OPTIONALLY ENCLOSED BY '"'
(DEPTNO,
DNAME,
LOC
)
BEGINDATA
10 Sales Virginia
20    Accounting    "Virginia,USA"
30     Consulting    Virginia

4.相关文件ppt_case2x.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT_P2X
TRUNCATE
--FIELDS TERMINATED BY WHITESPACE OPTIONALLY ENCLOSED BY '"'
FIELDS TERMINATED BY x'09' OPTIONALLY ENCLOSED BY '"'
(DEPTNO,
DNAME,
LOC
)
BEGINDATA
10 Sales Virginia
20    Accounting    "Virginia,USA"
30     Consulting    Virginia

5.以scott用户登录数据库,运行ppt_case2.sql初始化环境
6.在cmd命令下执行sqlldr加载ppt_case2w.ctl控制文件命令
7.查看sqlldr日志信息
8.查看sqlldr bad日志信息
9.查看数据库加载成功的数据

10.在cmd命令下执行sqlldr加载ppt_case2x.ctl控制文件命令
11.查看sqlldr日志信息
12.查看sqlldr bad日志信息
13.查看数据库加载成功的数据

四详细步骤操作

1.以scott用户登录数据库,运行ppt_case2.sql初始化环境
C:\sqlloader_exec\ppt_case2>sqlplus "scott/tiger"

SQL*Plus: Release 9.0.1.0.1 - Production on Sun Aug 5 14:15:39 2012

(c) Copyright 2001 Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> @ppt_case2
Disconnected from Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

C:\sqlloader_exec\ppt_case2>

2.在cmd命令下执行sqlldr加载ppt_case2w.ctl控制文件命令
C:\sqlloader_exec\ppt_case2>sqlldr userid=scott/tiger control=ppt_case2w.ctl

SQL*Loader: Release 9.0.1.1.1 - Production on Sun Aug 5 14:16:46 2012

(c) Copyright 2001 Oracle Corporation. All rights reserved.

Commit point reached - logical record count 2
Commit point reached - logical record count 3

C:\sqlloader_exec\ppt_case2>

3.查看sqlldr日志信息

SQL*Loader: Release 9.0.1.1.1 - Production on Sun Aug 5 14:27:10 2012

(c) Copyright 2001 Oracle Corporation. All rights reserved.

Control File:   ppt_case2w.ctl
Data File:      ppt_case2w.ctl
Bad File:     ppt_case2w.bad
Discard File: none specified

(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 DEPT_P2W, loaded from every logical record.
Insert option in effect for this table: TRUNCATE

   Column Name                  Position   Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO                              FIRST     * WHT O(") CHARACTER
DNAME                                NEXT     * WHT O(") CHARACTER
LOC                                  NEXT     * WHT O(") CHARACTER

Table DEPT_P2W:
3 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:                  49536 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             3
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Sun Aug 05 14:27:10 2012
Run ended on Sun Aug 05 14:27:11 2012

Elapsed time was:     00:00:01.29
CPU time was:         00:00:00.02

4.查看数据库加载成功的数据
C:\sqlloader_exec\ppt_case2>sqlplus "scott/tiger"

SQL*Plus: Release 9.0.1.0.1 - Production on Sun Aug 5 14:32:06 2012

(c) Copyright 2001 Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> set lines 120
SQL> select * from dept_p2w;

    DEPTNO DNAME                                              LOC
---------- -------------------------------------------------- -----------------
        10 Sales                                              Virginia
        20 Accounting                                         Virginia,USA
        30 Consulting                                         Virginia

SQL>

(控制文件中的源数据全部加载成功进scott.dept_p2w表中.)

5.在cmd命令下执行sqlldr加载ppt_case2x.ctl控制文件命令
C:\sqlloader_exec\ppt_case2>sqlldr userid=scott/tiger control=ppt_case2x.ctl

SQL*Loader: Release 9.0.1.1.1 - Production on Sun Aug 5 14:33:42 2012

(c) Copyright 2001 Oracle Corporation. All rights reserved.

Commit point reached - logical record count 2

C:\sqlloader_exec\ppt_case2>

6.查看sqlldr日志信息

SQL*Loader: Release 9.0.1.1.1 - Production on Sun Aug 5 14:33:42 2012

(c) Copyright 2001 Oracle Corporation. All rights reserved.

Control File:   ppt_case2x.ctl
Data File:      ppt_case2x.ctl
Bad File:     ppt_case2x.bad
Discard File: none specified

(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 DEPT_P2X, loaded from every logical record.
Insert option in effect for this table: TRUNCATE

   Column Name                  Position   Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO                              FIRST     * WHT O(") CHARACTER
DNAME                                NEXT     * WHT O(") CHARACTER
LOC                                  NEXT     * WHT O(") CHARACTER

Record 1: Rejected - Error on table DEPT_P2X, column DNAME.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 3: Rejected - Error on table DEPT_P2X, column DNAME.
Column not found before end of logical record (use TRAILING NULLCOLS)

Table DEPT_P2X:
1 Row successfully loaded.
2 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:                  49536 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             3
Total logical records rejected:         2
Total logical records discarded:        0

Run began on Sun Aug 05 14:33:42 2012
Run ended on Sun Aug 05 14:33:43 2012

Elapsed time was:     00:00:00.89
CPU time was:         00:00:00.03

7.查看sqlldr bad日志信息
10 Sales Virginia
30     Consulting    Virginia

8.查看数据库加载成功的数据
C:\sqlloader_exec\ppt_case2>sqlplus "scott/tiger"

SQL*Plus: Release 9.0.1.0.1 - Production on Sun Aug 5 14:36:16 2012

(c) Copyright 2001 Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> set lines 120
SQL> select * from dept_p2x;

    DEPTNO DNAME                                              LOC
---------- -------------------------------------------------- -----------------
        20 Accounting                                         Virginia,USA

SQL>

五个人总结

对比加载进入数据库中两个表的数据可知道:

SQL> select * from dept_p2w;

    DEPTNO DNAME                                              LOC
---------- -------------------------------------------------- ------------------
        10 Sales                                              Virginia
        20 Accounting                                         Virginia,USA
        30 Consulting                                         Virginia

SQL> select * from dept_p2x;

    DEPTNO DNAME                                              LOC
---------- -------------------------------------------------- ------------------
        20 Accounting                                         Virginia,USA

SQL>

当使用FIELDS TERMINATED BY WHITESPACE时,无论列间的空白间隔符号是什么都可以成功将数据导入.

而使用TERMINATED BY x'09'时,由于x'09' 表示为16进制的ascII码 09,即"TAB"制表符,所以只有第二行符合加载入库的条件(从日志报错中也可知道原因,sqlldr找不到做分隔符).

一个小结论也同样需要花很多的时间去实验验证.

六资料参考引用

http://afy.itpub.net/post/1128/22073

ASCII码表 http://baike.baidu.com/view/15482.htm

oracle中chr()和ascii()函数（附常用字符与ascii对照表）

http://blog.sina.com.cn/s/blog_9d5f7ceb01012i44.html

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11780477/viewspace-739872/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11780477/viewspace-739872/

cnk45946

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQLLOADER 2 terminal by whitespace or x'09'

一描述此档是sqlloader学习渐进的第二篇.区分全局指定FIELDS TERMINATED BY WHITESPACE 与FIELDS TERMINATED BY x'09' 的不同.二操...
复制链接

扫一扫