How to handle Oracle BLOB columns via Database Link

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

`1`	`-- General`

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

Example

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

Solution 2: Pipelining Functions

01CREATE TYPE object_row_type AS OBJECT (
02 MYID               NUMBER,
03 MYCLOB             CLOB             
04);
05  
06CREATE TYPE object_table_type AS TABLE OF object_row_type;
07  
08CREATE OR REPLACE FUNCTION GETCLOB RETURN object_table_type PIPELINED IS
09PRAGMA AUTONOMOUS_TRANSACTION;
10BEGIN
11 insert into LocalTempTableWithClob Select * from RemoteTableWithClob@RemoteServer;
12 commit;
13 FOR cur IN (SELECT myid, myclob from LocalTempTableWithClob)
14 LOOP
15   PIPE ROW(object_row_type(cur.myid, cur.myclob));
16 END LOOP;
17 DELETE FROM LocalTempTableWithClob;
18 COMMIT;
19 RETURN;
20END;
21  
22create view myRemoteData as
23SELECT * FROM TABLE(GetClob) a;
24  
25select myid, substr(myclob, 1, 5) mytext from myRemoteData;
26  
27MYID  MYTEXT
28--------------
29 1  This
30 2  Anoth
31 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:

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

Example

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