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 …