How to handle Oracle BLOB columns via Database Link

Published: Wednesday, 05 October 2016 22:57 | Written by Stefan | Print | Email | Hits: 88825

Oracle Developers are often faced with the problem to handle LOB data between several oracle instances via a database link. With plain SQL it is not allowed to select, insert or update a LOB on the remote system. This ends up with a error like this: "ORA-22992 cannot use lob locators selected from remote tables". There are three solutions for this

Solution 1: SQL Subquery

Thanks to user2015502 for this very smart solution in StackOverflow

-- General
SELECT (select <Blob-Column> from <Remote-Table>@<DB-Link> where ...) AS blob_column FROM DUAL;

Example

SELECT (
   select BLOB_COLUMN from SAMPLE_TABLE_WITH_BLOB@REMOTE_SERVER b where b.ID = a.ID
  ) AS BLOB_COLUMN
FROM LOCAL_TABLE a;

Solution 2: Pipelining Functions

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

Solution 3: Dynamic SQL (till Oracle 9)

I don't know why, but for insert/update till Oracle 9 the only way to access LOB columns is to use dynamic SQL. So a quite simple solution is:

-- for insert
excecute immediate 'insert into <Remote-Table>@<DB-Link> ( select * from <Local-Table> where ... )';
-- for update
execute immediate 'update <Remote-Table>@<DB-Link> set <BLOB-Column> = ( <Subselect ...>)';

Example

declare
  l_sql varchar2(2000);
  l_id number;
begin
  l_sql := 'update SAMPLE_TABLE_WITH_BLOB@REMOTE_SERVER set BLOB_COLUMN = (select BLOB_COLUMN from SAMPLE_TABLE_WITH_BLOB  where ID = :ID)';
  l_id := 7;
  execute immediate l_sql using l_id;
end;