oracle ora-22804,使用DBLINK访问LOB类型表-Oracle

This is a bit twisted solution, but works. Basically you first need to convert LOB (in these example I refer to CLOB, BLOB is similar but you will probably not use varchar2) to multiple varchar2(4000) on remote server – for example CLOB with size 8100 will be converted to 3 varchar2(4000), then read this data across db-links, then combine it back to CLOB on a local server.

REMOTE SERVER

SQL> create table ZWC.TableWithClob (myid number, myclob clob);

Table created.

SQL> insert into ZWC.TableWithClob (myid, myclob) values (1, 'test1');

1 row created.

SQL> insert into ZWC.TableWithClob (myid, myclob) values (2, 'test2');

1 row created.

SQL> insert into ZWC.TableWithClob (myid, myclob) values (3, 'test3');

1 row created.

SQL> commit;

Commit complete.

then we need to create a function for converting CLOB to multiple varchar2 and hide it behind view. Please note I tried to use collection here, but you cannot query collection across db-link, you will be facing error ORA-22804: remote operations not permitted on object tables or user-defined type columns thus I convert the clob to basically multi-row view (number of records is variable and depends on size of LOB).

SQL> CREATE TYPE object_row_type AS OBJECT (

2 MYORDER NUMBER,

3 MYID NUMBER,

4 MYCLOB_AS_VARCHAR VARCHAR2(4000));

5 /

Type created.

SQL> select tname from tab;

TNAME

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

TABLEWITHCLOB

EMP

SQL> CREATE TYPE object_table_type AS TABLE OF object_row_type;

2 /

Type created.

SQL> CREATE OR REPLACE FUNCTION CONVERT_CLOB_TO_VARCHAR RETURN object_table_type

2 PIPELINED IS

3 PRAGMA AUTONOMOUS_TRANSACTION;

4

5 v_clob_length number;

6 v_loops number;

7 v_varchar_size number := 4000;

8

9 BEGIN

10

11 FOR cur IN (SELECT myid, myclob from TableWithClob) LOOP

12 v_clob_length := dbms_lob.getlength(cur.MyClob);

13 v_loops := trunc(v_clob_length / v_varchar_size) +

14 sign(mod(v_clob_length, v_varchar_size)) - 1;

15

16 FOR i IN 0 .. v_loops LOOP

17 PIPE ROW(object_row_type(i + 1,

18 cur.myid,

19 dbms_lob.substr(cur.MyClob,

20 v_varchar_size,

21 v_varchar_size * i + 1)));

22 END LOOP;

23

24 END LOOP;

25 COMMIT;

26 RETURN;

27 END CONVERT_CLOB_TO_VARCHAR;

28 /

Function created.

SQL>

SQL> CREATE VIEW myRemoteData as

2 SELECT * FROM TABLE(CONVERT_CLOB_TO_VARCHAR) a;

View created.

LOCAL SERVER

SQL> create database link LOB_DBLINK

2 connect to dsg identified by dsg

3 using '(DESCRIPTION =

4 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.15)(PORT = 1521))

5 (CONNECT_DATA =

6 (SERVER = DEDICATED)

7 (SERVICE_NAME = zwc)

8 )

9 )';

Database link created.

SQL> select * from zwc.tablewithclob@LOB_DBLINK;

ERROR:

ORA-22992: cannot use LOB locators selected from remote tables

no rows selected

SQL> select count(*) from zwc.tablewithclob@LOB_DBLINK;

COUNT(*)

----------

3

SQL>

SQL> CREATE OR REPLACE FUNCTION F_VARCHAR_TO_CLOB(input_table_of_varchar myTableType)

2 RETURN CLOB IS

3 PRAGMA AUTONOMOUS_TRANSACTION;

4 v_clob clob;

5 BEGIN

6 FOR i IN 1 .. input_table_of_varchar.COUNT LOOP

7 v_clob := v_clob || input_table_of_varchar(i);

8 END LOOP;

9 RETURN v_clob;

10 END F_VARCHAR_TO_CLOB;

11 /

Function created.

SQL>

SQL> CREATE OR REPLACE VIEW myRemoteData as

2 SELECT a.myid,

3 F_VARCHAR_TO_CLOB( CAST(MULTISET(SELECT b.MYCLOB_AS_VARCHAR

4 FROM ZWC.myRemoteData@lob_dblink b

5 WHERE a.MYID = b.MYID

6 ORDER BY MYORDER ) as myTableType)

7 ) myClob

8 FROM ZWC.TABLEWITHCLOB@lob_dblink a;

View created.

SQL> select * from myRemoteData;

MYID

----------

MYCLOB

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

1

test1

2

test2

3

test3