0

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?

1 Answer 1

0

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)

1
  • I believe our query is in that format but the other way around. We are inserting from a dblink, not into a dblink. I'm positive it is the 'case when' part of the select that is causing this error but I don't understand why this could cause such a problem. You mentioning the temp table did gave me the solution though. As we are doing this with ODI I had to set the "USE_STAGING_TABLE" option in the LKM to true and now it works. Wish we didn't have to create an aditional table though.
    – R. Sluiter
    Nov 6, 2020 at 14:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.