Hi All,
Using Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
I'm selecting from a remote table that contains a column with a LOB datatype but I am NOT selecting that column.
But I am using a row_number to sort the values (again NOT using the column of datatype LOB as part of the row_number)
When I do the following - I get ORA-22992: cannot use LOB locators selected from remote tables
insert into table_x
(a1, c1, d1)
select (a, c ,d )
from
(select a,
b,
c,
d,
e,
row_number () over (partition by a order by b desc) as rno
from table a
)
where rno = 1;
I have been able to get around it by using an inline view and moving the row_number up a level so that it get's done on the local database but this obviously will impact performance.
insert into table_x
(a1, c1, d1)
select (a,c, d,
from (select a, b, c ,d e,
row_number () over (partition by a order by b desc) as rno)
from (select a,
b,
c,
d
from table a
)
)
where rno = 1;
Has anybody got any suggestions or know how to get around the original error message ?
Thanks
N.