Query CLOB or BLOB from oracle table across DB-LINK with a simple view – Solution 2

I will continue my two-part post with second a bit twisted solution. I did not try to optimize this for speed, it’s a simple proof of concept. This solution requires NO staging tables, but you need tiny bit more grants on remote server (you need to be able to create function, type and a view).

Please read Solution 1 first before you continue with Solution 2 and why you can get error ORA-22992: cannot use LOB locators selected from remote tables

.

SOLUTION 2

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. This can be all done on a fly without any staging tables.

.

1. REMOTE SERVER

let’s first define one table on remote server and load it with some data.

create table RemotetTableWithClob (myid number, myclob clob);

insert into RemotetTableWithClob (myid, myclob) values (1, 'This is small test');
insert into RemotetTableWithClob (myid, myclob) values (2, 'Another Test');
insert into RemotetTableWithClob (myid, myclob) values (3, 'One more test');
commit;

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). This is very simple

CREATE TYPE object_row_type AS OBJECT (
 MYORDER            NUMBER,
 MYID               NUMBER,
 MYCLOB_AS_VARCHAR  VARCHAR2(4000)    );

CREATE TYPE object_table_type AS TABLE OF object_row_type;

-- convert CLOB to multi-row varchar2
CREATE OR REPLACE FUNCTION CONVERT_CLOB_TO_VARCHAR RETURN object_table_type PIPELINED IS
PRAGMA AUTONOMOUS_TRANSACTION;

v_clob_length  number;
v_loops        number;
v_varchar_size number := 4000;

BEGIN

 FOR cur IN (SELECT myid, myclob from RemoteTableWithClob)
 LOOP
   v_clob_length := dbms_lob.getlength (cur.MyClob);
   v_loops := trunc(v_clob_length/v_varchar_size) + sign (mod(v_clob_length,v_varchar_size ) ) - 1;

   FOR i IN 0..v_loops
   LOOP
     PIPE ROW(object_row_type(i + 1, cur.myid, dbms_lob.substr( cur.MyClob, v_varchar_size, v_varchar_size * i + 1 )));
   END LOOP;

 END LOOP;
 COMMIT;
 RETURN;
END;

now the view on remote server

-- create view to show the clob converted to varchar
-- please notice there is important column MYORDER (order of individual varchar chunks)
CREATE VIEW myRemoteData as
SELECT * FROM TABLE(CONVERT_CLOB_TO_VARCHAR) a;

gg.

2. LOCAL SERVER

on a local server, I read the records and convert them to collection (this can be done in a query using CAST-MULTISET), then I use a function to convert this collection back to CLOB.

.

CREATE PUBLIC DATABASE LINK RemoteServer  USING 'remoteserver';
CREATE OR REPLACE TYPE myTableType  as table of varchar2(4000);

CREATE OR REPLACE FUNCTION JIRI.F_VARCHAR_TO_CLOB (input_table_of_varchar myTableType) RETURN CLOB IS
PRAGMA AUTONOMOUS_TRANSACTION;
v_clob  clob;
BEGIN
FOR i IN 1..input_table_of_varchar.COUNT
LOOP
 -- the concatenation below could be probably done using DBMS_LOB.WRITEAPPEND 
 v_clob := v_clob || input_table_of_varchar(i);
END LOOP;
RETURN v_clob;
END;

and here is the final view on local server

-- the view first converts separate varchar2 records to a collection
-- then function is used to convert the collection to CLOB
CREATE OR REPLACE VIEW myRemoteData as
SELECT a.myid,
       F_VARCHAR_TO_CLOB( CAST(MULTISET(SELECT b.MYCLOB_AS_VARCHAR
                                          FROM myRemoteData@remoteserver b
                                         WHERE a.MYID = b.MYID
                                      ORDER BY MYORDER   ) as myTableType)
                        ) myClob
FROM REMOTETABLEWITHCLOB@remoteserver a;

As you can see the process is very simple. I tested it with CLOB with more than 100,000 characters on my tiny VMWARE instance and got pretty decent timing. Please take the code above as a proof of concept, not as a tuned production ready solution.

Advertisement