Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Liu.

Asked: June 15, 2016 - 9:03 am UTC

Last updated: April 29, 2021 - 12:37 pm UTC

Version: 11.0.2.0

Viewed 10K+ times! This question is

You Asked

Tom,
Thanks for your time.

I have two dbs running 11.2.0. I create a DB link named dblk_b from DB_a to DB_b.
I have a table by name 'tableB' like below in database DB_b.

ID NUMBER(11),
CONTENT CLOB

I am trying to insert into tableB as follows

insert into tableB@dblk_b values (1,'a')

it's giving me error 'ORA 22992'

-Thx
-Charlia

and Chris said...

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)

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

What if another DB?

Asadulla, April 09, 2018 - 4:36 pm UTC

You answered: Loopback is a DB link pointing back to the same database.
Beautiful solution, but what if I do not use the same database?
I want to export lob data into mySQL database.
Got this:
ORA-02025: all tables in the SQL statement must be at the remote database.
Chris Saxon
April 10, 2018 - 1:25 pm UTC

What exactly is your query?

Note that if you're going from Oracle DB -> MySQL there are extra restrictions on what you can do over the link.

A reader, April 29, 2021 - 10:06 am UTC

Hi,

I have a requirement in which I need to insert the same row to a remote table that i insert to the local table. So we are creating a trigger to perform the insert into the remote table via db link. However the remote table contains a CLOB column by which the insert is not successful bit thoring error "ORA-22992: cannot use LOB locators selected from remote tables".

The trigger code is along the line mentioned below. Here table TEST_T exists in both the database and COL_B is of CLOB type.

create or replace trigger TRG_TEST_T

before insert or update or delete on TEST_T

for each row

begin

insert into TEST_T@DBLINK

(COL_A,COL_B)

VALUES(:NEW.COL_A,:NEW.COL_B;

end;

/

Any idea how to achieve this.

Regards;
Gopal
Chris Saxon
April 29, 2021 - 12:37 pm UTC

Try making it an after insert trigger:

create table t (
  c1 clob
);
create table t2 (
  c1 clob
);

create or replace trigger trig
after insert on t
for each row
begin
  insert into t2@loopback 
    values ( :new.c1 );
end;
/

insert into t values ( 'test' );

select * from t;

C1     
test 

select * from t2@loopback;

C1     
test

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here