inserting blob/clob over dblinks

Question

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 "TABLE_V" ("ID", "DATE", "CLOB_PT1", "CLOB_PT2", "CLOB", CONSTRAINT "TABLE_V_PK" PRIMARY KEY ("ID") RELY DISABLE) AS
  SELECT
  "ID",
  "DATE",
  UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(BLOB, 2000, 1)) "CLOB_PT1",
  UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(BLOB, 2000, 2001)) "CLOB_PT2",
  CASE WHEN LENGTH(INHOUD) > 4000 THEN TO_CLOB(BLOB) END "CLOB"
  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 >4000), same error.

Why is this failing? Any suggestions on getting this to work?

Answer 1

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 "insert@link .. select"

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)