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 we 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 to Explore

DBMS_LOB

More on PL/SQL routine DBMS_LOB here