由於LOB物件的特殊性，使得LOB物件的載入一般都是透過讀取檔案的方式，而不像其他型別那樣直接輸入。

簡單描述一下透過DBMS_LOB.LOADCLOBFROMFILE過程來載入CLOB欄位。

在10g以後，PL/SQL中可以直接處理長度小於32767的LOB內容，這時LOB被自動轉化為VARCHAR2型別。而如果長度超過了這個值，則無法直接賦值或訪問，需要透過DBMS_LOB包來進行處理。

而CLOB的載入除了透過DBMS_LOB包開啟後利用WRITE或WRITEAPPEND過程寫入資料外，還有一種直接載入檔案的方法，就是LOADCLOBFROMFILE過程。

SQL> CREATE TABLE T
2 (
3     ID NUMBER,
4     NAME VARCHAR2(30),
5     CONTENTS CLOB,
6     CONSTRAINT PK_T PRIMARY KEY (ID)
7 );

表已建立。

SQL> HOST echo 'test' > abc.txt

SQL> DECLARE
2     V_LOB CLOB;
3     V_FILE BFILE := BFILENAME('D_OUTPUT', 'abc.txt');
4     V_SOURCE NUMBER := 1;
5     V_DEST NUMBER := 1;
6     V_LANG NUMBER := 0;
7     V_WARN NUMBER;
8 BEGIN
9     INSERT INTO T
10             VALUES (1, 'TEST', EMPTY_CLOB)
11             RETURN CONTENTS INTO V_LOB;
12     DBMS_LOB.FILEOPEN(V_FILE);
13     DBMS_LOB.LOADCLOBFROMFILE(V_LOB,
14             V_FILE,
15             DBMS_LOB.LOBMAXSIZE,
16             V_DEST,
17             V_SOURCE,
18             0,
19             V_LANG,
20             V_WARN);
21     DBMS_LOB.FILECLOSEALL;
22     COMMIT;
23 END;
24 /

PL/SQL 過程已成功完成。

SQL> SELECT * FROM T;

ID NAME CONTENTS
---------- ---------- --------------------------------------------------------
1 TEST test

這時一個最簡單的例子，如果載入的文字檔案採用不同的編碼方式，比如UTF-8，則直接載入會導致亂碼。

比如utf.txt檔案就是一個UTF-8編碼的文字檔案，嘗試直接載入：

SQL> DECLARE
2     V_LOB CLOB;
3     V_FILE BFILE := BFILENAME('D_OUTPUT', 'utf.txt');
4     V_SOURCE NUMBER := 1;
5     V_DEST NUMBER := 1;
6     V_LANG NUMBER := 0;
7     V_WARN NUMBER;
8 BEGIN
9     INSERT INTO T
10             VALUES (2, 'UTF DIRECT', EMPTY_CLOB)
11             RETURN CONTENTS INTO V_LOB;
12     DBMS_LOB.FILEOPEN(V_FILE);
13     DBMS_LOB.LOADCLOBFROMFILE(V_LOB,
14             V_FILE,
15             DBMS_LOB.LOBMAXSIZE,
16             V_DEST,
17             V_SOURCE,
18             0,
19             V_LANG,
20             V_WARN);
21     DBMS_LOB.FILECLOSEALL;
22     COMMIT;
23 END;
24 /

PL/SQL 過程已成功完成。

SQL> SELECT * FROM T;

ID NAME CONTENTS
---------- ---------- --------------------------------------------------------
1 TEST test

2 UTF DIRECT 鍩縯est

LOADCLOBFROMFILE提供了字符集轉換的方法來解決這個問題，對於UTF-8字符集，可以設定字符集輸入引數為873：

SQL> DECLARE
2     V_LOB CLOB;
3     V_FILE BFILE := BFILENAME('D_OUTPUT', 'utf.txt');
4     V_SOURCE NUMBER := 1;
5     V_DEST NUMBER := 1;
6     V_LANG NUMBER := 0;
7     V_WARN NUMBER;
8 BEGIN
9     INSERT INTO T
10             VALUES (3, 'UTF TRAN', EMPTY_CLOB)
11             RETURN CONTENTS INTO V_LOB;
12     DBMS_LOB.FILEOPEN(V_FILE);
13     DBMS_LOB.LOADCLOBFROMFILE(V_LOB,
14             V_FILE,
15             DBMS_LOB.LOBMAXSIZE,
16             V_DEST,
17             V_SOURCE,
18             873,
19             V_LANG,
20             V_WARN);
21     DBMS_LOB.FILECLOSEALL;
22     COMMIT;
23 END;
24 /

PL/SQL 過程已成功完成。

SQL> SELECT * FROM T;

ID NAME CONTENTS
---------- ---------- -----------------------------------------------------
1 TEST test

2 UTF DIRECT 鍩縯est
3 UTF TRAN test

載入的uni.txt是一個UNICODE編碼的文字檔案，和上面的UTF-8編碼檔案一樣，都是透過Windows的notepad程式儲存為指定編碼的檔案，然後透過ftp的文字方式傳到當前節點的，這個檔案直接載入同樣會出現亂碼：

SQL> DECLARE
2     V_LOB CLOB;
3     V_FILE BFILE := BFILENAME('D_OUTPUT', 'uni.txt');
4     V_SOURCE NUMBER := 1;
5     V_DEST NUMBER := 1;
6     V_LANG NUMBER := 0;
7     V_WARN NUMBER;
8 BEGIN
9     INSERT INTO T
10             VALUES (4, 'UNI DIRECT', EMPTY_CLOB)
11             RETURN CONTENTS INTO V_LOB;
12     DBMS_LOB.FILEOPEN(V_FILE);
13     DBMS_LOB.LOADCLOBFROMFILE(V_LOB,
14             V_FILE,
15             DBMS_LOB.LOBMAXSIZE,
16             V_DEST,
17             V_SOURCE,
18             0,
19             V_LANG,
20             V_WARN);
21     DBMS_LOB.FILECLOSEALL;
22     COMMIT;
23 END;
24 /

PL/SQL 過程已成功完成。

SQL> SELECT * FROM T;

ID NAME CONTENTS
---------- ---------- ---------------------------------------------------
1 TEST test

         2 UTF DIRECT 鍩縯est
         3 UTF TRAN   test
         4 UNI DIRECT ？?

將字符集引數設定為1000後，可以正確的載入檔案：

SQL> DECLARE
2     V_LOB CLOB;
3     V_FILE BFILE := BFILENAME('D_OUTPUT', 'uni.txt');
4     V_SOURCE NUMBER := 1;
5     V_DEST NUMBER := 1;
6     V_LANG NUMBER := 0;
7     V_WARN NUMBER;
8 BEGIN
9     INSERT INTO T
10             VALUES (5, 'UNI TRAN', EMPTY_CLOB)
11             RETURN CONTENTS INTO V_LOB;
12     DBMS_LOB.FILEOPEN(V_FILE);
13     DBMS_LOB.LOADCLOBFROMFILE(V_LOB,
14             V_FILE,
15             DBMS_LOB.LOBMAXSIZE,
16             V_DEST,
17             V_SOURCE,
18             1000,
19             V_LANG,
20             V_WARN);
21     DBMS_LOB.FILECLOSEALL;
22     COMMIT;
23 END;
24 /

PL/SQL 過程已成功完成。

SQL> SELECT * FROM T;

ID NAME CONTENTS
---------- ---------- ------------------------------------------------------
1 TEST test

         2 UTF DIRECT 鍩縯est
         3 UTF TRAN   test
         4 UNI DIRECT ？?
         5 UNI TRAN   test

利用PLSQL包載入CLOB欄位

相關文章