利用PLSQL包載入CLOB欄位

由於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