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
2 | SELECT ( select <Blob- Column > from <Remote- Table >@<DB-Link> where ...) AS blob_column FROM DUAL; |
Example
2 | select BLOB_COLUMN from SAMPLE_TABLE_WITH_BLOB@REMOTE_SERVER b where b.ID = a.ID |
Solution 2: Pipelining Functions
01 | CREATE TYPE object_row_type AS OBJECT ( |
06 | CREATE TYPE object_table_type AS TABLE OF object_row_type; |
08 | CREATE OR REPLACE FUNCTION GETCLOB RETURN object_table_type PIPELINED IS |
09 | PRAGMA AUTONOMOUS_TRANSACTION; |
11 | insert into LocalTempTableWithClob Select * from RemoteTableWithClob@RemoteServer; |
13 | FOR cur IN ( SELECT myid, myclob from LocalTempTableWithClob) |
15 | PIPE ROW(object_row_type(cur.myid, cur.myclob)); |
17 | DELETE FROM LocalTempTableWithClob; |
22 | create view myRemoteData as |
23 | SELECT * FROM TABLE (GetClob) a; |
25 | select myid, substr(myclob, 1, 5) mytext from myRemoteData; |
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:
2 | excecute immediate 'insert into <Remote-Table>@<DB-Link> ( select * from <Local-Table> where ... )' ; |
4 | execute immediate 'update <Remote-Table>@<DB-Link> set <BLOB-Column> = ( <Subselect ...>)' ; |
Example
5 | l_sql := 'update SAMPLE_TABLE_WITH_BLOB@REMOTE_SERVER set BLOB_COLUMN = (select BLOB_COLUMN from SAMPLE_TABLE_WITH_BLOB where ID = :ID)' ; |
7 | execute immediate l_sql using l_id; |