Oracle SQL*Loader使用案例（三）

SQL*Loader對(duì)不同文件及格式的處理方法

3.1多個(gè)數(shù)據(jù)文件，導(dǎo)入同一張表

      通常對(duì)于邏輯比較復(fù)雜的系統(tǒng)可能存在這種情況，因?yàn)閷?dǎo)出的數(shù)據(jù)來(lái)源于多個(gè)系統(tǒng)， 因此可能提供給DBA的也是多個(gè)數(shù)據(jù)文件。這種情況并不一定需要執(zhí)行多次加載，只需要在控制文件中做適當(dāng)配置即可。不過(guò)有一點(diǎn)非常重要，提供的數(shù)據(jù)文件中的數(shù)據(jù)存放格式必須完全相同。

創(chuàng)建演示表tb_manager表

--創(chuàng)建tb_manager表  

SCOTT@seiang11g>create table tb_manager(mgrno number,mname varchar2(30),job varchar2(300),remark varchar2(4000));

Table created.

有多個(gè)數(shù)據(jù)文件，分別如下：

--數(shù)據(jù)文件1  
[oracle@wjq SQL*Loader]$ vim wjq_test8_1.dat
10,SMITH,SALES MANAGER
11,ALLEN.W,TECH MANAGER
16,BLAKE,HR MANAGER
18,WJQ,TEACHER MASTER
  
--數(shù)據(jù)文件2  
[oracle@wjq SQL*Loader]$ vim wjq_test8_2.dat 
12,WARD,SERVICE MANAGER
13,TURNER,SELLS DIRECTOR
15,JAMES,HR DIRECTOR
  
--數(shù)據(jù)文件3  
[oracle@wjq SQL*Loader]$ vim wjq_test8_3.dat 
17,MILLER,PRESIDENT

創(chuàng)建控制文件，制定多個(gè)INFILE參數(shù)即可，控制文件如下：

--控制文件  
[oracle@wjq SQL*Loader]$ vim wjq_test8.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test8_1.dat'
INFILE '/u01/app/oracle/SQL*Loader/wjq_test8_2.dat'
INFILE '/u01/app/oracle/SQL*Loader/wjq_test8_3.dat'
TRUNCATE INTO TABLE tb_manager
FIELDS TERMINATED BY ","
(
 MGRNO,MNAME,JOB
)

執(zhí)行sqlldr命令，并查看結(jié)果

--執(zhí)行sqlldr命令  
[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test8.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 15:45:43 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 4
Commit point reached - logical record count 7
Commit point reached - logical record count 8

--查看結(jié)果  

SCOTT@seiang11g>select * from tb_manager;

     MGRNO MNAME                JOB                            REMARK
---------- -------------------- ------------------------------ ------------------------------
        10 SMITH                SALES MANAGER
        11 ALLEN.W              TECH MANAGER
        16 BLAKE                HR MANAGER
        18 WJQ                  TEACHER MASTER
        12 WARD                 SERVICE MANAGER
        13 TURNER               SELLS DIRECTOR
        15 JAMES                HR DIRECTOR
        17 MILLER               PRESIDENT

3.2同一個(gè)數(shù)據(jù)文件，導(dǎo)入不同表

控制文件提供了多種邏輯判斷方式，只要能把邏輯清晰地描述出來(lái)，SQL*Loader就能 按照指定的邏輯執(zhí)行加載。

數(shù)據(jù)文件如下：

--數(shù)據(jù)文件  

[oracle@wjq SQL*Loader]$ cat wjq_test9.dat
BON  SMITH CLEAK       3904 
BON  ALLEN SALER,M     2891 
BON  WARD  SALER,"S"   3128 
BON  KING  PRESIDENT   2523 
MGR  10 SMITH    SALES MANAGER 
MGR  11 ALLEN.W  TECH MANAGER 
MGR  16 BLAKE    HR MANAGER 
TMP  SMITH 7369 CLERK    1020 20 
TMP  ALLEN 7499 SALESMAN 1930 30 
TMP  WARD  7521 SALESMAN 1580 30 
TMP  JONES 7566 MANAGER  3195 20

需求是將MGR開(kāi)頭的記錄導(dǎo)入到tb_manager表，以BON開(kāi)頭的記錄導(dǎo)入到tb_loader表，其他記錄存放到廢棄文件中，創(chuàng)建控制文件如下:

--控制文件  

[oracle@wjq SQL*Loader]$ cat wjq_test9.ctl
LOAD DATA 
INFILE '/u01/app/oracle/SQL*Loader/wjq_test9.dat'
DISCARDFILE '/u01/app/oracle/SQL*Loader/wjq_test9.dsc'
TRUNCATE
INTO TABLE tb_loader
WHEN TAB='BON' 
( 
 TAB FILLER POSITION(1:3), 
 ENAME POSITION(6:10), 
 JOB POSITION(*+1:20), 
 SAL POSITION(*+3:27) 
) 
INTO TABLE tb_manager
WHEN TAB='MGR' 
( 
 TAB FILLER POSITION(1:3), 
 MGRNO POSITION(6:7), 
 MNAME POSITION(9:15), 
 JOB POSITION(*+2:30) 
) 

雖然這個(gè)控制文件看起來(lái)比之前的都要復(fù)雜，但只有一個(gè)新語(yǔ)法，即關(guān)鍵字，我們這里通過(guò)WHEN來(lái)實(shí)現(xiàn)判斷，很容易理解。同時(shí)，指定了DISCARDFILE參數(shù)，以生成不滿足加載條件的廢棄文件，如果你有心，不妨等執(zhí)行完sqlldr命令后査看wjq_test9.dsc文件和wjq_test9.log文件。
    另外注意，控制文件中WHEN邏輯判斷不支持OR關(guān)鍵字，因此如果你的判斷條件有多個(gè)，則只能通過(guò)AND連接，而不能直接使用OR。

執(zhí)行sqlldr命令，并查看結(jié)果

--執(zhí)行sqlldr命令  

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test9.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 15:55:48 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 11

--查看結(jié)果  

SCOTT@seiang11g>select * from tb_loader;

ENAME      JOB                                   SAL       COMM
---------- ------------------------------ ---------- ----------
SMITH      CLEAK                                3904
ALLEN      SALER,M                              2891
WARD       SALER,"S"                            3128
KING       PRESIDENT                            2523

SCOTT@seiang11g>select * from tb_manager;

     MGRNO MNAME                JOB                            REMARK
---------- -------------------- ------------------------------ ------------------------------
        10 SMITH                SALES MANAGER
        11 ALLEN.W              TECH MANAGER
        16 BLAKE                HR MANAGER

這里貼一下log日志和廢棄日志

--log日志  

[oracle@wjq SQL*Loader]$ cat wjq_test9.log

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 15:55:48 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Control File:   /u01/app/oracle/SQL*Loader/wjq_test9.ctl
Data File:      /u01/app/oracle/SQL*Loader/wjq_test9.dat
  Bad File:     /u01/app/oracle/SQL*Loader/wjq_test9.bad
  Discard File: /u01/app/oracle/SQL*Loader/wjq_test9.dsc
 (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 TB_LOADER, loaded when TAB = 0X424f4e(character 'BON')
Insert option in effect for this table: TRUNCATE

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
TAB                                   1:3     3           CHARACTER           
  (FILLER FIELD)
ENAME                                6:10     5           CHARACTER           
JOB                             NEXT+1:20    19           CHARACTER           
SAL                             NEXT+3:27    24           CHARACTER           

Table TB_MANAGER, loaded when TAB = 0X4d4752(character 'MGR')
Insert option in effect for this table: TRUNCATE

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
TAB                                   1:3     3           CHARACTER           
  (FILLER FIELD)
MGRNO                                 6:7     2           CHARACTER           
MNAME                                9:15     7           CHARACTER           
JOB                             NEXT+2:30    28           CHARACTER           

Record 8: Discarded - failed all WHEN clauses.
Record 9: Discarded - failed all WHEN clauses.
Record 10: Discarded - failed all WHEN clauses.
Record 11: Discarded - failed all WHEN clauses.

Table TB_LOADER:
  4 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  7 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Table TB_MANAGER:
  3 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  8 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                   7168 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:            11
Total logical records rejected:         0
Total logical records discarded:        4

Run began on Tue Oct 31 15:55:48 2017
Run ended on Tue Oct 31 15:55:48 2017

Elapsed time was:     00:00:00.08
CPU time was:         00:00:00.02

--廢棄日志  

[oracle@wjq SQL*Loader]$ cat wjq_test9.dsc
TMP  SMITH 7369 CLERK    1020 20 
TMP  ALLEN 7499 SALESMAN 1930 30 
TMP  WARD  7521 SALESMAN 1580 30 
TMP  JONES 7566 MANAGER  3195 20

3.3數(shù)據(jù)文件前N行不想導(dǎo)入

假如某天你接到一項(xiàng)數(shù)據(jù)加載需求，用戶提供了一份100萬(wàn)行的數(shù)據(jù)文件，告訴你只導(dǎo)后50萬(wàn)行，恭喜，你接到了一個(gè)正常的需求！
實(shí)現(xiàn)的方式較多，比如修改數(shù)據(jù)文件，只保留后50萬(wàn)行（Windows下借助EditPlus 這類文本工具可以輕松實(shí)現(xiàn)，Linux/UNIX下通過(guò)TAIL等命令也可以輕易實(shí)現(xiàn)），如果你人很懶，不想修改文件，那正合sqlldr胃口，人家早早地就提供好了SKIP參數(shù)專用于滿足此類需求。

數(shù)據(jù)文件如下：

--數(shù)據(jù)文件  

[oracle@wjq SQL*Loader]$ vim wjq_test10.dat
#This is data of emp
ENAME        MGR JOB             SAL
---------- ----- --------- ---------
SMITH       7902 CLERK         1020
  LEN       7698 SALESMAN      1930
▽ARD        7698 SALESMAN      1580
JONES       7839 MANAGER       3195
MARTIN      7698 SALESMAN      1580
BLAKE       7839 MANAGER       3180
CLARK       7839 MANAGER       2172
SCOTT       7566 ANALYST       3220
KING             PRESIDENT     4722
TURNER      7698 SALESMAN      1830
ADAMS       7788 CLERK         1320
JAMES       7698 CLERK         1280
FORD        7566 ANALYST       3220
MILLER      7782 CLERK         1022

我們只對(duì)該數(shù)據(jù)文件只從第4行開(kāi)始導(dǎo)入，即前3行不進(jìn)行導(dǎo)入，創(chuàng)建控制文件如下：

--控制文件  

[oracle@wjq SQL*Loader]$ vim wjq_test10.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test10.dat' 
TRUNCATE INTO TABLE tb_loader
( 
 ENAME position(1:6),
 XCOL FILLER position(13:16),
 JOB position(18:26),
 SAL position(32:35)
)

執(zhí)行sqlldr命令，并查看結(jié)果

--執(zhí)行sqlldr命令  

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test10.ctl skip=3

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 16:12:11 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 14

--查看結(jié)果  

SCOTT@seiang11g>select * from tb_loader;

ENAME      JOB                                   SAL       COMM
---------- ------------------------------ ---------- ----------
SMITH      CLERK                                1020
ALLEN      SALESMAN                             1930
WARD       SALESMAN                             1580
JONES      MANAGER                              3195
MARTIN     SALESMAN                             1580
BLAKE      MANAGER                              3180
CLARK      MANAGER                              2172
SCOTT      ANALYST                              3220
KING       PRESIDENT                            4722
TURNER     SALESMAN                             1830
ADAMS      CLERK                                1320
JAMES      CLERK                                1280
FORD       ANALYST                              3220
MILLER     CLERK                                1022

如果用戶要求較高，明確指定只加載第XX到第XX行的記錄，sqlldr還有一個(gè)參數(shù)叫LOAD,配置LOAD參數(shù)即可輕松實(shí)現(xiàn)。
這里仍使用上述數(shù)據(jù)文件，需求改為只導(dǎo)入第4到9行的記錄，我們連控制文件都不需要修改，只需要在執(zhí)行sqlldr時(shí)再加上LOAD參數(shù)即可：
執(zhí)行sqlldr命令(即skip=3跳過(guò)前3行，load=6，加載接下來(lái)的6行記錄)，并查看結(jié)果

--執(zhí)行sqlldr命令  

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test10.ctl skip=3 load=6

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 16:15:13 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 6

--查看結(jié)果  

SCOTT@seiang11g>select * from tb_loader;

ENAME      JOB                                   SAL       COMM
---------- ------------------------------ ---------- ----------
SMITH      CLERK                                1020
ALLEN      SALESMAN                             1930
WARD       SALESMAN                             1580
JONES      MANAGER                              3195
MARTIN     SALESMAN                             1580
BLAKE      MANAGER                              3180

3.4加載的數(shù)據(jù)中有換行符

由于標(biāo)準(zhǔn)換行符也是sqlldr識(shí)別數(shù)據(jù)行結(jié)束的標(biāo)志符，因此要將含換行符的數(shù)據(jù)加載到表中稍復(fù)雜一點(diǎn)點(diǎn)，而且需要根據(jù)實(shí)際情況來(lái)處理，不同情況的處理方式也不一樣， 但基本思路是相同的，就是要同sqlldr指明什么時(shí)候才需要進(jìn)行換行操作。

3.4.1手工指定的換行符

在手工指定換行符的情況下，數(shù)據(jù)文件中的換行符并不是標(biāo)準(zhǔn)的換行標(biāo)志，而是用戶自定義的一個(gè)標(biāo)識(shí)字符（或多個(gè)字符組成），這種情況的處理比較簡(jiǎn)單，如數(shù)據(jù)文件如下：

--數(shù)據(jù)文件  

[oracle@wjq SQL*Loader]$ vim wjq_test11_1.dat
10,SMITH,SALES MANAGER,This is SMITH.\nHe is a Sales Manager.
11,ALLEN.W,TECH MANAGER,This is ALLEN.W.\nHe is a Tech Manager.
16,BLAKE,HR MANAGER,This is BLAKE.\nHe is a Hr Manager.

我們可以通過(guò)控制文件，在數(shù)據(jù)加載前處理remark列的數(shù)據(jù)，將用戶指定的字符替換為chr(10),即標(biāo)準(zhǔn)換行符，創(chuàng)建控制文件如下：

--控制文件  

[oracle@wjq SQL*Loader]$ vim wjq_test11_1.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test11_1.dat' 
TRUNCATE INTO TABLE tb_manager
FIELDS TERMINATED BY ","
(
 MGRNO,
 MNAME,
 JOB,
 REMARK "REPLACE(:remark,'\\n',chr(10))"
)

這里需要注意的是，替換時(shí)必須指定"\\n"而不只是"\n"，因?yàn)?span>"\n"會(huì)被SQLLDR 識(shí)別成換行符并轉(zhuǎn)換成換行標(biāo)志，這樣可能導(dǎo)致數(shù)據(jù)加載出錯(cuò)。而是默認(rèn)轉(zhuǎn)義符，指定該轉(zhuǎn)義符后sqlldr就會(huì)將"\n"識(shí)別成普通字符了。

執(zhí)行sqlldr命令，并查看結(jié)果

--執(zhí)行sqlldr命令  

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test11_1.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 16:21:04 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 3

--查看結(jié)果  

SCOTT@seiang11g>select * from tb_manager;

     MGRNO MNAME                JOB                            REMARK
---------- -------------------- ------------------------------ ------------------------------
        10 SMITH                SALES MANAGER                  This is SMITH.
                                                               He is a Sales Manager.

        11 ALLEN.W              TECH MANAGER                   This is ALLEN.W.
                                                               He is a Tech Manager.

        16 BLAKE                HR MANAGER                     This is BLAKE.
                                                               He is a Hr Manager.

3.4.2指定FIX屬性處理?yè)Q行符

數(shù)據(jù)文件如下：

--數(shù)據(jù)文件  

[oracle@wjq SQL*Loader]$ cat wjq_test11_2.dat
10 SMITH   SALES MANAGER THIS IS SMITH. 
HE IS A SALES MANAGER.
11 ALLEN.W TECH MANAGER  THIS IS ALLEN.W
HE IS A TECH MANAGER.
16 BLAKE   HR MANAGER    THIS IS BLAKE. 
HE IS A HR MANAGE.   

（特別注意：因?yàn)槭褂玫?span>FIX固定長(zhǎng)度，所以一定要注意每一行數(shù)據(jù)的長(zhǎng)度，不夠的使用空格來(lái)代替，否則在加載的使用會(huì)報(bào)錯(cuò)，報(bào)錯(cuò)信息如下所示：

SQL*Loader-501: Unable to read file (wjq_test11_2.dat)

SQL*Loader-566: partial record found at end of datafile

SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.

報(bào)錯(cuò)的原因并不是說(shuō)讀不到要加載的數(shù)據(jù)文件，相反，雖然出現(xiàn)了報(bào)錯(cuò)的信息，也有可能部分?jǐn)?shù)據(jù)已經(jīng)導(dǎo)入進(jìn)去了，報(bào)錯(cuò)因?yàn)槟愕拇嬖跀?shù)據(jù)并不是你所FIX的長(zhǎng)度，所以出現(xiàn)報(bào)錯(cuò)，故在使用FIX的時(shí)候一定要注意這一點(diǎn)）

創(chuàng)建控制文件如下：

--控制文件  

[oracle@wjq SQL*Loader]$ vim wjq_test11_2.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test11_2.dat' "FIX 65" 
TRUNCATE INTO TABLE tb_manager
(
 MGRNO POSITION(1:2),
 MNAME POSITION(*+1:10),
 JOB POSITION(*+1:24),
 REMARK POSITION(*+1:63)
)

FIX是INFILE關(guān)鍵字的一個(gè)屬性，INFILE不僅有FIX屬性，還有VAR和STR等屬性

執(zhí)行sqlldr命令，并查看結(jié)果

--執(zhí)行sqlldr命令

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test11_2.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Wed Nov 1 10:37:45 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 3

--查看結(jié)果  

SCOTT@seiang11g>select * from tb_manager;

     MGRNO MNAME                JOB                            REMARK
---------- -------------------- ------------------------------ ----------------------------------------
        10 SMITH                SALES MANAGER                  THIS IS SMITH.
                                                               HE IS A SALES MANAGER.

        11 ALLEN.W              TECH MANAGER                   THIS IS ALLEN.W.
                                                               HE IS A TECH MANAGER.

        16 BLAKE                HR MANAGER                     THIS IS BLAKE.
                                                               HE IS A HR MANAGER.

這種方式其實(shí)就是在加載數(shù)據(jù)文件之前，先通過(guò)FIX屬性指定每行的長(zhǎng)度（這里每行65個(gè)字符，包括換行符在內(nèi),該例子上不夠字符的用空格來(lái)代替了，注意上面數(shù)據(jù)文件中的結(jié)尾的空格），到了指定長(zhǎng)度就換行，不管中間有沒(méi)有換行符，因此僅能用于定長(zhǎng)字符串的數(shù)據(jù)文件，因?yàn)橹挥凶址ㄩL(zhǎng)，你才知道應(yīng)該在INFILE處指定什么值。

針對(duì)上述的數(shù)據(jù)文件也可以通過(guò)使用CONCATENATE屬性處理?yè)Q行符來(lái)處理?yè)Q行符，控制文件內(nèi)容如下所示：
[oracle@wjq SQL*Loader]$ vim wjq_test11_2.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test11_2.dat' 
CONCATENATE 2 INTO TABLE tb_manager        
(
 MGRNO POSITION(1:2),
 MNAME POSITION(*+1:10),
 JOB POSITION(*+1:24),
 REMARK POSITION(*+1:63) "REPLACE(upper(:REMARK),'HE',chr(10)||'HE')"
)

3.4.3指定VAR屬性處理?yè)Q行符（行頭部標(biāo)識(shí)換行）

前面提到INFILE關(guān)鍵字還支持VAR屬性，語(yǔ)法格式為INFILE filename "var n", n的值不能超過(guò)40,否則會(huì)報(bào)錯(cuò)，如果不指定n則默認(rèn)值為5。
本小節(jié)就演示通過(guò)這種方式處理?yè)Q行符?？偟膩?lái)說(shuō)，這確實(shí)是相當(dāng)有才的一種方式， 首先通過(guò)VAR屬性在每行開(kāi)頭指定一個(gè)固定長(zhǎng)度的字符串，該字符串指明該行的長(zhǎng)度，通過(guò)這種方式支持變長(zhǎng)字符串。

數(shù)據(jù)文件如下：

--數(shù)據(jù)文件  

[oracle@wjq SQL*Loader]$ vim wjq_test11_3.dat
06110,SMITH,SALES MANAGER,This is SMITH.
He is a Sales Manager.
06311,ALLEN.W,TECH MANAGER,This is ALLEN.W.
He is a Tech Manager.
05516,BLAKE,HR MANAGER,This is BLAKE.
He is a Hr Manager.

數(shù)據(jù)文件中每行開(kāi)頭的061，063，055分別表示該行取61，63，55個(gè)字符

創(chuàng)建控制文件如下：

--控制文件  

[oracle@wjq SQL*Loader]$ vim wjq_test11_3.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test11_3.dat' "var 3" 
TRUNCATE INTO TABLE tb_manager
FIELDS TERMINATED BY ","
(MGRNO,MNAME,JOB,REMARK)

執(zhí)行sqlldr命令，并查看結(jié)果

--執(zhí)行sqlldr命令  

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test11_3.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Wed Nov 1 09:51:12 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 3

--查看結(jié)果  

SCOTT@seiang11g>select * from tb_manager;

     MGRNO MNAME                JOB                            REMARK
---------- -------------------- ------------------------------ ----------------------------------------
        10 SMITH                SALES MANAGER                  This is SMITH.
                                                               He is a Sales Manager.

        11 ALLEN.W              TECH MANAGER                   This is ALLEN.W.
                                                               He is a Tech Manager.

        16 BLAKE                HR MANAGER                     This is BLAKE.
                                                               He is a Hr Manager.

①這種方式可以視為第2種方式的變種，該方法不在INFILE處指定行長(zhǎng)度，而改為在每行的行首位置標(biāo)注該行長(zhǎng)度，以支持變長(zhǎng)類型的數(shù)據(jù)格式。

②綜合來(lái)看，這兩種方式實(shí)用價(jià)值都比較低，先不說(shuō)用戶是否真有如此耐心來(lái)生成這樣格式的數(shù)據(jù)文件，就算用戶按照這種格式提供，要知道windows、Linux/UNIX下因操作系統(tǒng)自身原因,對(duì)換行符識(shí)別也不同，在Windows環(huán)境下?lián)Q行標(biāo)志由"回車chr(13)+換行chr(10)" 兩個(gè)字節(jié)組成，而Linux/UNIX環(huán)境則是"換行chr(10)"一個(gè)字節(jié)，也就是說(shuō)指定了長(zhǎng)度，操作系統(tǒng)變了以后，長(zhǎng)度有可能也得跟著變，這就造成用戶提供的數(shù)據(jù)文件通用性較差。

③有沒(méi)有一種更簡(jiǎn)單的方式呢？事實(shí)上確實(shí)沒(méi)有太簡(jiǎn)單的方式，在前面的內(nèi)容中就提到， 你需要為sqlldr指明什么時(shí)候應(yīng)該換行，因此對(duì)數(shù)據(jù)文件進(jìn)行預(yù)處理是必然的，不過(guò)相對(duì)來(lái)講，下面將要介紹的方式更易于操作，也更可行一些。

3.4.4指定STR屬性處理?yè)Q行符（行尾部標(biāo)識(shí)換行）

這種方式也需要先對(duì)數(shù)據(jù)文件做處理，在記錄換行處打上一個(gè)標(biāo)記，比如"丨"（當(dāng)然可以定義為其他字符，但注意不要與要導(dǎo)入的數(shù)據(jù)有沖突），這樣sqlldr見(jiàn)到該字符就知道換行的時(shí)候到了。
    由于單個(gè)字符出現(xiàn)在導(dǎo)入數(shù)據(jù)中的機(jī)率較高，因此建議換行標(biāo)志盡可能由多個(gè)字符組成，通常習(xí)慣于定義“字符+換行符”作為新的換行標(biāo)記，這里我們也采用這種方式。

數(shù)據(jù)文件和控制文件如下：

--數(shù)據(jù)文件  

[oracle@wjq SQL*Loader]$ vim wjq_test11_4.dat
10,SMITH,SALES MANAGER,This is SMITH.
He is a Sales Manager.|
11,ALLEN.W,TECH MANAGER,This is ALLEN.W.
He is a Tech Manager.|
16,BLAKE,HR MANAGER,This is BLAKE.
He is a Hr Manager.|

--控制文件  

[oracle@wjq SQL*Loader]$ vim wjq_test11_4.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test11_4.dat' "str '|\n'" 
TRUNCATE INTO TABLE tb_manager
FIELDS TERMINATED BY ","
(MGRNO,MNAME,JOB,REMARK)

執(zhí)行sqlldr命令，并查看結(jié)果

--執(zhí)行sqlldr命令  

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test11_4.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Wed Nov 1 09:45:28 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 3

--查看結(jié)果  

SCOTT@seiang11g>select * from tb_manager;

     MGRNO MNAME                JOB                            REMARK
---------- -------------------- ------------------------------ ----------------------------------------
        10 SMITH                SALES MANAGER                  This is SMITH.
                                                               He is a Sales Manager.

        11 ALLEN.W              TECH MANAGER                   This is ALLEN.W.
                                                               He is a Tech Manager.

        16 BLAKE                HR MANAGER                     This is BLAKE.
                                                               He is a Hr Manager.

STR屬性中支持兩種字符指定方式：
'char_string':普通字符，即標(biāo)準(zhǔn)的可見(jiàn)字符，不過(guò)也有些不可見(jiàn)字符可以通過(guò)下列反斜杠標(biāo)識(shí)的方式在字符串模式中指定：
\n:表示換行。
\t:表示行制表符(tab)。
\f:表示換頁(yè)。
\v:表示列制表符。
\r:表示回車。
    說(shuō)到這里，又不得不再次提及Windows和Linux/UNIX對(duì)換行符識(shí)別的差異，Linux/UNIX下指定"\n"即可，Windows下需要指定"\r\n"才表示一個(gè)完整的換行符。
    X'hex_string':二進(jìn)制字符。對(duì)于一些不可見(jiàn)字符，如像回車換行這類字符，可以將其轉(zhuǎn)換成十六進(jìn)制，然后再通過(guò)str X'hex_str'方式指定。

比如上述控制文件中的功能如果用二進(jìn)制字符表示，形式如下:

INFILE ldr_case11_4.dat "str X'7C0A'"  

要査看指定字符的十六進(jìn)制編碼，可以通過(guò)UTL_RAW.CAST_TO_RAW生成，例如:

SCOTT@seiang11g>select utl_raw.cast_to_raw('|'||chr(10)) from dual;

UTL_RAW.CAST_TO_RAW('|'||CHR(10))
--------------------------------------------------------------------------------
7C0A

這種方式相當(dāng)于自定義一個(gè)換行標(biāo)志，標(biāo)準(zhǔn)換行符不再擁有特殊的意義，只是作為要加載數(shù)據(jù)的一部分。較第一種方式而言最大的優(yōu)勢(shì)是，數(shù)據(jù)文件相對(duì)更容易處理，只需要在生成數(shù)據(jù)文件時(shí)，最后一列附加一個(gè)指定字符即可，對(duì)于稍有SQL基礎(chǔ)的人來(lái)說(shuō)，這都是小case。

 
作者：SEian.G（苦練七十二變，笑對(duì)八十一難）