ORA-00949: illegal reference to remote database with catsearch [message #552587] |
Thu, 26 April 2012 13:14 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9092 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.
|
|
|
|