執行遠端DBMS_LOB包中過程

簡單描述執行遠端的DBMS_LOB包的一些問題。

 

 

由於LOB的定位符無法跨資料庫鏈使用，因此訪問遠端LOB物件的時候，需要呼叫遠端的DBMS_LOB包中的過程，但是測試發現，即使呼叫遠端的DBMS_LOB包中的過程，仍然可能出現問題。

SQL> CREATE TABLE T_LOB (ID NUMBER, CONTENTS CLOB);

表已建立。

SQL> DECLARE
  2     V_LOB CLOB;
  3  BEGIN
  4     INSERT INTO T_LOB
  5     VALUES (1, EMPTY_CLOB())
  6     RETURN  CONTENTS INTO V_LOB;
  7     FOR I IN 1..100 LOOP
  8        DBMS_LOB.WRITEAPPEND(V_LOB, 32767, LPAD('A', 32767, 'A'));
  9     END LOOP;
 10  END;
 11  /

PL/SQL 過程已成功完成。

SQL> COMMIT;

提交完成。

SQL> SELECT DBMS_LOB.GETLENGTH(CONTENTS) FROM T_LOB;

DBMS_LOB.GETLENGTH(CONTENTS)
----------------------------
                     3276700

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> SELECT * FROM GLOBAL_NAME;

GLOBAL_NAME
----------------------------------------------------------------------------
TESTZJ

如果在遠端資料庫中，透過資料庫鏈獲取這個LOB欄位的長度，直接透過DBMS_LOB.GETLENGTH肯定是行不通的：

SQL> SELECT * FROM GLOBAL_NAME;

GLOBAL_NAME
----------------------------------------------------------------------------------
TEST08

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> CREATE DATABASE LINK TESTZJ
  2  CONNECT TO TEST
  3  IDENTIFIED BY TEST
  4  USING '172.25.13.231/TESTZJ';

Database link created.

SQL> SELECT COUNT(*) FROM T_LOB@TESTZJ;

  COUNT(*)
----------
         1

SQL> SELECT DBMS_LOB.GETLENGTH(CONTENTS) FROM T_LOB@TESTZJ;
SELECT DBMS_LOB.GETLENGTH(CONTENTS) FROM T_LOB@TESTZJ
                          *
ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables

嘗試呼叫遠端的DBMS_LOB包，但是仍然會導致錯誤的產生：

SQL> SELECT DBMS_LOB.GETLENGTH@TESTZJ(CONTENTS) FROM T_LOB@TESTZJ;
SELECT DBMS_LOB.GETLENGTH@TESTZJ(CONTENTS) FROM T_LOB@TESTZJ
                                 *
ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables

按道理來說，雖然T_LOB是遠端物件，但是DBMS_LOB呼叫的也是遠端過程，LOB定位符的使用並沒有跨越資料庫，因此應該是可以得到結果的。

在11.2中，Oracle已經可以利用這種方法來進行訪問了：

SQL> SELECT * FROM GLOBAL_NAME;

GLOBAL_NAME
---------------------------------------------------------------------------------
TEST112

SQL> SELECT * FROM V$VERSION;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> CREATE DATABASE LINK TESTZJ
  2  CONNECT TO TEST
  3  IDENTIFIED BY TEST
  4  USING '172.25.13.231/TESTZJ';

資料庫連結已建立。

SQL> SELECT COUNT(*) FROM T_LOB@TESTZJ;

  COUNT(*)
----------
         1

SQL> SELECT DBMS_LOB.GETLENGTH@TESTZJ(CONTENTS) FROM T_LOB@TESTZJ;

DBMS_LOB.GETLENGTH@TESTZJ(CONTENTS)
-----------------------------------
                            3276700

SQL> SELECT LENGTH(CONTENTS) FROM T_LOB@TESTZJ;

LENGTH(CONTENTS)
----------------
         3276700

在11g中，甚至可以透過LENGTH函式直接獲取遠端LOB的長度。

雖然10.2中不能透過遠端的DBMS_LOB直接訪問表中的LOB，但是還是可以利用這個包處理函式返回的LOB。

對於低版本的情況，為了可以使用遠端的DBMS_LOB包，需要在遠端站點建立一個函式，返回LOB物件：

SQL> SELECT * FROM GLOBAL_NAME;

GLOBAL_NAME
---------------------------------------------------------------------------------
TESTZJ

SQL> CREATE OR REPLACE FUNCTION F_LOB (P_ID IN NUMBER)
  2  RETURN CLOB AS
  3     V_LOB CLOB;
  4  BEGIN
  5     SELECT CONTENTS
  6     INTO V_LOB
  7        FROM T_LOB
  8     WHERE ID = P_ID;
  9     RETURN V_LOB;
 10  END;
 11  /

函式已建立。

下面透過呼叫F_LOB來處理LOB的長度：

SQL> SELECT * FROM GLOBAL_NAME;

GLOBAL_NAME
-------------------------------------------------------------------------
TEST08

SQL> SELECT DBMS_LOB.GETLENGTH@TESTZJ(F_LOB@TESTZJ(1)) FROM T_LOB@TESTZJ;

DBMS_LOB.GETLENGTH@TESTZJ(F_LOB@TESTZJ(1))
------------------------------------------
                                   3276700

SQL> SELECT LENGTH(F_LOB@TESTZJ(1)) FROM T_LOB@TESTZJ;

LENGTH(F_LOB@TESTZJ(1))
-----------------------
                3276700

透過在遠端先呼叫F_LOB返回CLOB後，就可以透過遠端的DBMS_LOB.GETLENGTH來獲取長度了。

在呼叫遠端F_LOB並返回CLOB後，甚至可以直接透過LENGTH來獲取LOB欄位的長度。

從這一點上看，LENGTH和DBMS_LOB.GETLENGTH要擁有更多的優勢。