Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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

NualaJan 23 2017 — edited Jan 23 2017

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.

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 20 2017
Added on Jan 23 2017
8 comments
1,130 views