ORA-22992: cannot use LOB locators selected from remote tables

select dbms_lob.substr(col_clob, 32000, 1) from xyz@dblink; -- ORA-22992: cannot use LOB locators selected from remote tables -- Make sure dbms_lob.substr is executed remotely -- by suffixing it with @dblink: select dbms_lob.substr@dblink(col_clob, 32000, 1) from xyz@dblink; …

ORA-22992, Database links, CTAS and ROW_NUMBER() analytic function

For a reason I don't understand, the following scenario threw an ORA-22992 error (Oracle 19.03 and 19.16).

On the remote database, two tables are created:

create table tq84_A (
   id  number  ( 5) not null,
   txt varchar2(10)
);

create table tq84_B (
   id  number  ( 5) not null,
   clb clob
);

Then, on the local database, I try to create table like so (19.3 and 19.16):

create table tq84_C as
select
   row_number() over (order by 1) r
from
   tq84_A@dblink a                 join
   tq84_B@dblink b on a.id = b.id
;

This throws a ORA-22992 although the clob of tq84_B is not involved.

The table can be created if I don't use the analytic function:

create table tq84_C as
select
   1 r
from
   tq84_A@dblink a                 join
   tq84_B@dblink b on a.id = b.id
;

When I drop the clb column on the remote database, I can also create the table with the row_number() function:

alter table tq84_B drop column clb;

create table …

ORA-22992: cannot use LOB locators selected from remote tables

ORA-22992, Database links, CTAS and ROW_NUMBER() analytic function

See also