Home » Server Options » Text & interMedia » ORA-00949: illegal reference to remote database with catsearch (Oracle 10.2.0.4/ OS- SUNW,SPARC-Enterprise-T5220)
ORA-00949: illegal reference to remote database with catsearch [message #552587] Thu, 26 April 2012 13:14 Go to next message
lkngstr82is
Messages: 33
Registered: January 2010
Location: USA
Member
We are getting following error while executing SELECT with UNION ALL on subqueries with catsearch.

The following query runs fine:
            select seq_nb
            from BS_SEQ_DATA
            where name_id in
            (select name_id from BS_NM_DATA where CatSearch@vista(NM_ADRS_TX, 'CHENG', '') > 0)
            
            union all
            
            select shrt_seq_nb seq_nb
            from bs_shrt_seq_data
            where shrt_name_id in
            (select shrt_nm_id from bs_shrt_nm_data where CatSearch@vista(shrt_nm, 'CHENG', '') > 0)

But as soon as I wrap another select around it:


      select * from
      (
            select seq_nb
            from BS_SEQ_DATA
            where name_id in
            (select name_id from BS_NM_DATA where CatSearch@vista(NM_ADRS_TX, 'CHENG', '') > 0)
            
            union all
            
            select shrt_seq_nb seq_nb
            from bs_shrt_seq_data
            where shrt_name_id in
            (select shrt_nm_id from bs_shrt_nm_data where CatSearch@vista(shrt_nm, 'CHENG', '') > 0)      )


I get:
Error: ORA-00949: illegal reference to remote database

Re: ORA-00949: illegal reference to remote database with catsearch [message #552594 is a reply to message #552587] Thu, 26 April 2012 14:29 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7937
Registered: November 2002
Location: California, USA
Senior Member
You should provide create table and insert statements for sample data and post a copy and paste of an actual run from SQL*Plus, complete with line numbers, so that we can see what you are actually running. I am guessing that you typed this, instead of copying and pasting, since you left out the database link after the table name in the from clause of your sub-queries that use catsearch. However, even if you fix that, I suspect you have encountered:

bug 7306881: TEXT SEARCH CAN'T USE ROWNUM IN A NESTED QUERY FROM A REMOTE DATABASE

Although you are not using rownum directly, I believe that when you use set operators like union all, that Oracle does so internally.


Re: ORA-00949: illegal reference to remote database with catsearch [message #552596 is a reply to message #552594] Thu, 26 April 2012 15:37 Go to previous message
lkngstr82is
Messages: 33
Registered: January 2010
Location: USA
Member
You are not seeing db link name for remote table because we are using public synonym [over dblink]for this table
Previous Topic: PLS-00201: identifier 'CTX_THES' must be declared, while creating thesaurus
Next Topic: issue with Contains() in Oracle 9i (merged 3)
Goto Forum:
  


Current Time: Tue Jul 29 15:22:53 CDT 2014

Total time taken to generate the page: 0.10552 seconds