OraFAQ Forum: Text & interMedia » ORA-00949: illegal reference to remote database with catsearch

Home » Server Options » Text & interMedia » ORA-00949: illegal reference to remote database with catsearch (Oracle 10.2.0.4/ OS- SUNW,SPARC-Enterprise-T5220)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-00949: illegal reference to remote database with catsearch [message #552587]

Thu, 26 April 2012 13:14

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

Report message to a moderator

Re: ORA-00949: illegal reference to remote database with catsearch [message #552594 is a reply to message #552587]

Thu, 26 April 2012 14:29

Barbara Boehmer
Messages: 9104
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.

Report message to a moderator

Re: ORA-00949: illegal reference to remote database with catsearch [message #552596 is a reply to message #552594]

Thu, 26 April 2012 15:37

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

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 06 23:30:32 CST 2025