Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, hong.

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

Answered by: Chris Saxon - Last updated: September 16, 2020 - 5:30 pm UTC

Category: SQL - 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
 a.class=1
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 11.2.0.2.0 - 64bit Production
Remote version:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 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;

C1   
    1 
    2 
    3 
    4 
    5 
    6 
    7 
    8 
    9 
   10 


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

and you rated our response

  (2 ratings)

Reviews

Convert to Classic join works

September 16, 2020 - 1:47 pm UTC

Reviewer: Hong from New York

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(+)
and...

Chris Saxon

Followup  

September 16, 2020 - 2:51 pm UTC

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

solved

September 16, 2020 - 2:59 pm UTC

Reviewer: Hong from New York

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

Chris Saxon

Followup  

September 16, 2020 - 5:30 pm UTC

Happy to help

More to Explore

Administration

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