ORA-949 from CONTAINS query with ORDER BY or ROWNUM in subquery over DBLINK

(Doc ID 1380489.1)

Last updated on NOVEMBER 28, 2011

Applies to:

Oracle Text - Version: 10.1.0.2 to 11.2.0.3 - Release: 10.1 to 11.2
Information in this document applies to any platform.

Symptoms

A CONTAINS query over dblink with ORDER BY fails with an ORA-00949:

SQL> select * from
  2  (
  3  select id from remtab@remdb
  4  where CONTAINS@remdb(text,'hello') > 0
  5  ORDER BY id
  6  )
  7  where rownum < 50;
where CONTAINS@remdb(text,'hello') > 0
*
ERROR at line 4:
ORA-00949: illegal reference to remote database

Similarly, a CONTAINS query over dblink with ROWNUM fails with an ORA-00949:

SQL> select rn from
  2  (
  3  select ROWNUM rn
  4  from remtab@remdb
  5  where CONTAINS@remdb(text,'hello') > 0
  6  );
where CONTAINS@remdb(text,'hello') > 0
*
ERROR at line 4:
ORA-00949: illegal reference to remote database

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms