使用SQL*Loader匯入CLOB和BLOB資料使用案例

      在使用SQL*Loader往資料庫中匯入blob或clob資料總令人有些頭疼。本文詳細記錄了用Oracle的SQL*Loader實現該功能的一個實驗案例，希望能對大家有幫助。

首先建立一張用於實驗的測試表

SCOTT@seiang11g>create table tb_clob(new_id number(20),new_to varchar2(100),new_subject varchar2(100),new_date date,new_content clob);
Table created.

下面是資料檔案中的內容：

[oracle@wjq SQL*Loader]$ vim wjq_clob.csv
1,wjq123@qq.com,"Greeting from Mars",2017-11-02 10:59:43,\/u01\/app\/oracle\/SQL*Loader\/new_clob001.dat
2,seiang@126.com,"Special discount",2017-11-02 11:28:55,\/u01\/app\/oracle\/SQL*Loader\/new_clob002.dat
(特別注意：一定要注意資料檔案的格式，不能有多餘的空格存在，作者在這裡栽過好多次的坑，否則匯入的時候會報錯，所以執行匯入之後最好檢視一下日誌資訊)

new_content是訊息正文，用clob資料型別表示。在資料檔案中該欄位僅儲存每條記錄的訊息正文的clob檔名。new_clob001.dat,，new_clob002.dat檔案中儲存new_content的真正內容。

new_clob001.dat檔案中的內容：

[oracle@wjq SQL*Loader]$ cat new_clob001.dat
China's top judicial authorities presented long-anticipated reports on Wednesday to national legislators on their progress in upholding the law and preventing wrongful convictions in the wake of important judicial reforms in 2013.

The Supreme People's Court and Supreme People's Procuratorate both submitted reports to the bimonthly session of the Standing Committee of the National People's Congress on Wednesday.The top court said it had overturned 37 wrongful convictions since November 2012, including in the high-profile case of Nie Shubin, who was exonerated on Dec 2, 2016, more than two decades after he was wrongly executed for rape and murder.

new_clob001.dat檔案中的內容：

[oracle@wjq SQL*Loader]$ cat new_clob002.dat  
Thanks to these efforts, courts acquitted 4,032 defendants in accordance with the law between 2013 and September this year, the report said.

Courts have also been ordered to strictly exclude evidence obtained illegally, including evidence gained by torture, "and not to force anyone to plead guilty", Zhou said.In Shanghai, for example, between July 2016 and September this year, the city's courts received 24 applications from defense attorneys to strike evidence suspected to have been obtained illegally, leading to 15 reviews on the legality of evidence, he said.

匯入上述的資料需要寫如下的控制檔案：

[oracle@wjq SQL*Loader]$ cat wjq_clob.ctl
LOAD DATA  
INFILE '/u01/app/oracle/SQL*Loader/wjq_clob.csv' 
TRUNCATE INTO TABLE tb_clob 
FIELDS TERMINATED BY ',' 
( new_id       CHAR(20), 
  new_to       CHAR(100),   
  new_subject  CHAR(100), 
  new_date     DATE "YYYY-MM-DD HH24:MI:SS" ":new_date",
  clob_filename FILLER CHAR(1000000), 
  new_content  LOBFILE(clob_filename) TERMINATED BY EOF 
)

上述控制檔案的關鍵在於定義一個clob_filename的偽欄位(在SQL*Loader中就是filler)以獲取clob檔名，緊接著用lobfile從該檔案匯入訊息正文。如果需要匯入blob型別的資料，其方法完全一樣。

下面就開始執行匯入操作：

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

SQL*Loader: Release 11.2.0.4.0 - Production on Thu Nov 2 11:43:54 2017

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

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

下面檢視一下日誌資訊：

Table TB_CLOB:
  2 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:                1000488 bytes(1 rows)
Read   buffer bytes: 1048576

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

Run began on Thu Nov 02 11:43:54 2017
Run ended on Thu Nov 02 11:43:54 2017

Elapsed time was:     00:00:00.05
CPU time was:         00:00:00.00

透過日誌可以看到，匯入成功，下面透過查詢表中的內容來進行驗證