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

This is not so unusual scenario: You have a table with one or more LOB fields on remote oracle server and you want to query that table across db-link. If you try simple SELECT statement you will be facing ORA-22992: cannot use LOB locators selected from remote tables

oooops, what next. There are several workarounds, let me try to tackle one easy solution.

This testing was done using oracle 10g running on remote windows machine and 11.2 running on local redhat5 server, but it should in general work for any 10g/11g servers.

.

for the second solution follow this link

.

.

SOLUTION 1

The first solution is actually very simple. Create a table (I prefer temporary table) on local oracle server, insert data from remote server to local server using INSERT INTO and then query local server. This can be all fully automated and hidden from users …

1. REMOTE SERVER

let’s first define one table on remote server and load it with some data, that’s all we need to do on remote server.

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;

.

2. LOCAL SERVER

first, let me create db-link (I have same userID on both servers, this will be passwordless db-link) and then try to run simple query

CREATE PUBLIC DATABASE LINK RemoteServer  USING 'remoteserver';

select myid, substr(myclob, 1, 5) mytext from remotetablewithclob@RemoteServer;
ORA-22992: cannot use LOB locators selected from remote tables

as you can see I cannot query the CLOB field.

.

3. SIMPLE WORKAROUND

The simply workaround is to bring the data from remote server to local server and then query local table. I use temporary tables simply because I don’t want to worry about deleting the data and concurrent users.

create global temporary table LocalTempTableWithClob
 (myid number,  myclob clob) ON COMMIT PRESERVE ROWS;

insert into  LocalTempTableWithClob Select * from RemoteTableWithClob@RemoteServer;
select  myid, substr(myclob, 1, 5) mytext from LocalTempTableWithClob;

MYID   MYTEXT
--------------
 1  This
 2  Anoth
 3  One m

.

4. NOW THE REAL TRICK

As you can see (3) works pretty good and it can be the end of the post, but … what if you have multiple users who want to query the data. Are you going to ask them to run the insert every time the want to query the table?

Here is my solution to that problem. I basically use a function to insert data from remote to local table and then display the result of local table instead. This is all done in a single pass in a single function and can be hidden from users via view.

I use pipelined function because I really love them and hope more users are going to start using them.

CREATE TYPE object_row_type AS OBJECT (
 MYID               NUMBER,
 MYCLOB             CLOB             );

CREATE TYPE object_table_type AS TABLE OF object_row_type;

CREATE OR REPLACE FUNCTION GETCLOB RETURN object_table_type PIPELINED IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
 insert into LocalTempTableWithClob Select * from RemoteTableWithClob@RemoteServer;
 commit;
 FOR cur IN (SELECT myid, myclob from LocalTempTableWithClob)
 LOOP
   PIPE ROW(object_row_type(cur.myid, cur.myclob));
 END LOOP;
 DELETE FROM LocalTempTableWithClob;
 COMMIT;
 RETURN;
END;

create view myRemoteData as
SELECT * FROM TABLE(GetClob) a;

select myid, substr(myclob, 1, 5) mytext from myRemoteData;

MYID  MYTEXT
--------------
 1  This
 2  Anoth
 3  One m

As you can see the solution is very simple, the function is hidden behind the view and users do not even know about the data insert.

Please note this is a bit oversimplified scenario, you can easily extend the function to add custom WHERE clause (which I would highly recommend, you don’t want to move whole table over dblink every single time), you can even make the function very universal for any querying of LOB across db-link.

About these ads