Skip to Main Content


Question and Answer

Chris Saxon

Thanks for the question, hong.

Asked: September 16, 2020 - 12:47 pm UTC

Last updated: September 16, 2020 - 5:30 pm UTC

Version: 11

Viewed 100+ times

You Asked

I have a SQL statement can run by itself and get the result back over db link. But if I want to put result set into a table either using “create table….as..” or “insert into …” before the same select statement, I will get ORA-22992 error. What caused this?

The SQL statement like:

Select a.m, a.n, a.o, a.p, b.q, b.r, b.s, c.t, c.u,c.v
From a@remote a left join b@remote b on b.m=a.m and b.n=a.n
 Left join c@remote c on c.m=a.m and c.m=a.n
Where a.yr=2019
order by a.m

table “a” and “c” don’t have LOB fields, table “b” has a field “Ldesc” which is CLOB, But it is NOT in the select list.

Local version :
Oracle Database 11g Enterprise Edition Release - 64bit Production
Remote version:
Oracle Database 11g Enterprise Edition Release - 64bit Production

and we said...

I suspect this is an ANSI join transformation issue. Converting to classic joins works for me:

create table t1 (
  c1 int, c2 clob
create table t2 (
  c1 int, c2 clob

insert into t1
with rws as (
  select level x from dual
  connect by level <= 10
  select x, 'clob data' from rws;
insert into t2
  select * from t1;
create table tmpt as 
  select c1 
  from   t1@loopback t1
  join   t2@loopback t2
  using   ( c1 );
ORA-22992: cannot use LOB locators selected from remote tables

create table tmpt as 
  select t1.c1 
  from   t1@loopback t1, t2@loopback t2
  where  t1.c1 = t2.c1;

select * from tmpt;


The JOIN ... USING form of the query works in 19c, so there may be a patch available.


  (2 ratings)


Convert to Classic join works

Hong, September 16, 2020 - 1:47 pm UTC

But with a few fields to join for the left join, do we just do?

where a.m=b.m(+)
and a.n = b.n(+)

Chris Saxon
September 16, 2020 - 2:51 pm UTC

Yes, you need to add (+) to all the columns of the table you're outer joining.


Hong, September 16, 2020 - 2:59 pm UTC

Thank you so much, Chris, for your time and your expertise! It really helps a lot!

Chris Saxon
September 16, 2020 - 5:30 pm UTC

Happy to help

More to Explore


Need more information on Administration? Check out the Administrators guide for the Oracle Database