Use DBLINK to access a LOB table

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 into SS 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 within SS 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------------------------------TABLEWITHCLOBEMPSQL> 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 tablesno rows selectedSQL> select count(*) from zwc.tablewithclob@LOB_DBLINK;  COUNT(*)----------         3SQL> 

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--------------------------------------------------------------------------------         1test1         2test2         3test3