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