利用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

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-625105/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-625105/