Execute remote DBMS_LOB package in Oracle

Briefly describes some of the problems with performing a DBMS_LOB package at the distal end.

Because the LOB locator cannot be used across the database chain, it is necessary to call the procedure in the remote DBMS_LOB package when accessing the remote LOB object, but the test found that the problem may still occur even if the procedure in the remote Dbms_lob package is invoked.

Sql> CREATE TABLE T_lob (ID number, CONTENTS CLOB);

Table has been created.

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;

Ten end;

11/

The PL/SQL process has completed successfully.

Sql> COMMIT;

Submit completed.

Sql> SELECT Dbms_lob. GetLength (CONTENTS) from T_lob;

Dbms_lob. GetLength (CONTENTS)

----------------------------

3276700

Sql> SELECT * from V$version;

BANNER

----------------------------------------------------------------

Oracle database10genterpriseedition Release10.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

This column more highlights: http://www.bianceng.cn/database/Oracle/

If in the remote database, get the length of the LOB field through the database chain, directly through the Dbms_lob. GetLength must not be workable:

Sql> SELECT * from Global_name;

Global_name

----------------------------------------------------------------------------------

TEST08

Sql> SELECT * from V$version;

BANNER

----------------------------------------------------------------

Oracle database10genterpriseedition Release10.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

Attempt to call the Dbms_lob package at the far end, but still cause the error to occur:

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

In principle, although T_lob is a remote object, the Dbms_lob is called a remote procedure, and the use of LOB locators does not span the database, so it should be possible to get results.