測試TOM=SQLLOADER載入製表符號資料

用字表符分割的資料，有兩種載入辦法

1.TERMINATED BY X '09'

2.TERMINATED BY WHITESPACE

=============以WHITESPACE來測試製表符資料的載入=================

會話1：清空測試表資料，建立新的控制檔案載入新的帶有製表符號的資料。

SQL> select * from dept_load;

DEPTNO DNAME LOC

---------- -------------- -------------

10 sales abc,usa

20 aaa xx,"yy"

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> truncate table dept_load;

Table truncated.

SQL>

會話1：建立新的控制檔案如下

[oracle@oraclelinux ~]$ ls -ltr dept*

-rw-r--r-- 1 oracle oinstall 202 May 11 16:02 dept_load2.ctl

-rw-r--r-- 1 oracle oinstall 1679 May 11 16:03 dept_load2.log

-rw-r--r-- 1 oracle oinstall 1 May 11 16:03 dept_load2.bad

[oracle@oraclelinux ~]$ rm -rf dept*

[oracle@oraclelinux ~]$ ls -ltr dept*

ls: dept*: No such file or directory

[oracle@oraclelinux ~]$ vi dept_load3.ctl

load data

infile *

into table dept_load

replace

fields terminated by whitespace

(deptno,dname,loc)

begindata

10 sales virginia

~

會話1：載入新資料

[oracle@oraclelinux ~]$ sqlldr userid=scott/scott control=dept_load3.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Mon May 14 10:34:02 2012

Commit point reached - logical record count 1

[oracle@oraclelinux ~]$ ls -ltr dept8

ls: dept8: No such file or directory

[oracle@oraclelinux ~]$ ls -ltr dept*

-rw-r--r-- 1 oracle oinstall 129 May 14 10:32 dept_load3.ctl

-rw-r--r-- 1 oracle oinstall 1548 May 14 10:34 dept_load3.log

[oracle@oraclelinux ~]$ exit

exit

SQL> select * from dept_load;

DEPTNO DNAME LOC

---------- -------------- -------------

10 sales virginia

SQL> !

[oracle@oraclelinux ~]$ more dept_load3.log

SQL*Loader: Release 10.2.0.1.0 - Production on Mon May 14 10:34:02 2012

Control File: dept_load3.ctl

Data File: dept_load3.ctl

Bad File: dept_load3.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_LOAD, loaded from every logical record.

Insert option in effect for this table: REPLACE

Column Name Position Len Term Encl Datatype

------------------------------ ---------- ----- ---- ---- ---------------------

DEPTNO FIRST * WHT CHARACTER

DNAME NEXT * WHT CHARACTER

LOC NEXT * WHT CHARACTER

Table DEPT_LOAD:

1 Row 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: 1

Total logical records rejected: 0

Total logical records discarded: 0

Run began on Mon May 14 10:34:02 2012

Run ended on Mon May 14 10:34:02 2012

Elapsed time was: 00:00:00.59

CPU time was: 00:00:00.07

[oracle@oraclelinux ~]$

=====================使用16進位制格式的製表符號====================

會話1：清空測試表DEPT_LOAD,建立新控制檔案

[oracle@oraclelinux ~]$ ls -ltr dept*

-rw-r--r-- 1 oracle oinstall 129 May 14 10:32 dept_load3.ctl

-rw-r--r-- 1 oracle oinstall 1548 May 14 10:34 dept_load3.log

[oracle@oraclelinux ~]$ cp dept_load3.ctl dept_load4.ctl

[oracle@oraclelinux ~]$ rm -rf dept_load3.*

[oracle@oraclelinux ~]$ vi dept_load4.ctl

1 load data

2 infile *

3 into table dept_load

4 replace

5 fields terminated by X'09'====16進位制的製表符號表示方法

6 (deptno,dname,loc)

7 begindata

8 10 sales virginia

~

[oracle@oraclelinux ~]$ sqlldr userid=scott/scott control=dept_load4.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Mon May 14 10:42:54 2012

Commit point reached - logical record count 1

[oracle@oraclelinux ~]$ exit

exit

SQL> select * from dept_load;

DEPTNO DNAME LOC

---------- -------------- -------------

10 sales

SQL>

測試結果發現在此，一旦SQLLDR 遇到一個製表符，就會輸出一個值。因此，將10 賦給DEPTNO，DNAME 得到了NULL，

因為在第一個製表符和製表符的下一次出現之間沒有資料。Sales 賦給了LOC。

這個時候如果我們把資料中的製表符號由兩個變成一個，那麼DNAME就會正常顯示

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15720542/viewspace-723490/，如需轉載，請註明出處，否則將追究法律責任。

測試TOM=SQLLOADER載入製表符號資料

相關文章