My Remarks!

the Life, the Universe and almost Everything what's Left

How to handle Oracle BLOB columns via Database Link

BlobFishOracle 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 it is only possible to access LOB columns with 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;