Skip to Main Content


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


Viewed 10K+ times! This question is

You Asked

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.


I am trying to insert into tableB as follows

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

it's giving me error 'ORA 22992'


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;

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;

  l clob;
  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;
  insert into t@loopback
    select * from gtt;

select id, length(x) from t;

        ID  LENGTH(X)
---------- ----------
         1      40000

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


  (2 ratings)

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


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


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


insert into TEST_T@DBLINK





Any idea how to achieve this.

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
  insert into t2@loopback 
    values ( :new.c1 );

insert into t values ( 'test' );

select * from t;


select * from t2@loopback;


More to Explore


More on PL/SQL routine DBMS_LOB here