inserting blob/clob over dblinks I'm trying to find the best way to load a remote table with a blob column (blob only contains text, so clob would have been better).
So far we've been able to reduce loadtime from 218 minutes to 2 minutes using a view on the source table and converting the blob to varchar2 using (simplified):
CREATE OR REPLACE FORCE EDITIONABLE VIEW &quot;TABLE_V&quot; (&quot;ID&quot;, &quot;DATE&quot;, &quot;CLOB_PT1&quot;, &quot;CLOB_PT2&quot;, &quot;CLOB&quot;, CONSTRAINT &quot;TABLE_V_PK&quot; PRIMARY KEY (&quot;ID&quot;) RELY DISABLE) AS
  SELECT
  &quot;ID&quot;,
  &quot;DATE&quot;,
  UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(BLOB, 2000, 1)) &quot;CLOB_PT1&quot;,
  UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(BLOB, 2000, 2001)) &quot;CLOB_PT2&quot;,
  CASE WHEN LENGTH(INHOUD) &gt; 4000 THEN TO_CLOB(BLOB) END &quot;CLOB&quot;
  FROM OWNER.TABLE;

Because we cannot guarantee the size of the blob will continue to fit in our two varchars we put anything bigger into a separate clob column in the view.
now the idea is to merge the varchars or clob back to one clob in our staging area.
Here come's the challenge of ORA-22992: cannot use LOB locators selected from remote tables
Concatenating the two varchars into the targeted clob column works fine.
INSERT 
  /*+  APPEND PARALLEL  */ 
  INTO SA.TESTTABLE
  (
    ID ,
    DATE ,
    CLOB
  ) 
SELECT 
  TABLE_V_1.ID ,
  TABLE_V_1.DATE , 
  TABLE_V_1.CLOB_PT1||TABLE_V_1.CLOB_PT2
FROM 
  OWNER.TABLE_V_1@DB_LNK TABLE_V_1  

Inserting the clob into the targeted clob column works fine.
INSERT 
  /*+  APPEND PARALLEL  */ 
  INTO SA.TESTTABLE
  (
    ID ,
    DATE ,
    CLOB
  ) 
SELECT 
  TABLE_V_1.ID ,
  TABLE_V_1.DATE , 
  CLOB
FROM 
  OWNER.TABLE_V_1@DB_LNK TABLE_V_1  

Using a 'case when' inserting either the concatenated varchars or the clob column fails!
INSERT 
  /*+  APPEND PARALLEL  */ 
  INTO SA.TESTTABLE
  (
    ID ,
    DATE ,
    CLOB
  ) 
SELECT 
  TABLE_V_1.ID ,
  TABLE_V_1.DATE , 
  CASE WHEN CLOB IS NOT NULL
    THEN TABLE_V_1.CLOB_PT1||TABLE_V_1.CLOB_PT2
    ELSE TABLE_V_1.INHOUD_CLOB
  END
FROM 
  OWNER.TABLE_V_1@DB_LNK TABLE_V_1  

I also tried concatenating all three columns by default, (varchars1 and 2 will be empty if size blob &gt;4000), same error.
Why is this failing?
Any suggestions on getting this to work?
To insert lobs over a database link, your inserts need to be in the form:
INSERT INTO table1@remote_site SELECT * FROM local_table;
http://docs.oracle.com/database/121/ADLOB/adlob_working.htm#ADLOB45148
You can do this by:

Creating a temporary table
Loading your clob data into the temp table
The insert over the DB link with &quot;insert@link .. select&quot;

For example:
create table t (
id int,
x clob
);
create global temporary table gtt (
id int,
x clob
) on commit preserve rows;
declare
l clob;
begin
insert into gtt values ( 1, empty_clob() )
returning x into l;
for i in 1 .. 10 loop
dbms_lob.writeappend(l, 4000, lpad('x', 4000, 'x'));
end loop;
commit;
insert into t@loopback
select * from gtt;
end;
/
select id, length(x) from t;
    ID  LENGTH(X)


     1      40000

(Loopback is a DB link pointing back to the same database)