My Remarks!

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

Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in /mnt/web018/d2/82/51207682/htdocs/armbruster-it/joomla33/templates/stefans_blog_05/functions.php on line 182

me

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

1-- General
2SELECT (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;

Visits

Today 23 | Week 23 | Month 3396 | All 1148671

Challenges Completed

Vätternsee Club des Cinglés du Mont-Ventoux Styrkeproven 24hVelo Belchen³ Guitar Rehearsal

StackOverflow

profile for PT_STAR at Stack Overflow, Q&A for professional and enthusiast programmers