Below is the query I am trying to run
from xyzl@OACNV T
and T.setid = 'GBL'
and not exists (select 'X'
from xyz X
where X.id = T.id
and X.code = rtrim(T.code,' ')
and X.effdt = T.effdt)
************ORA-02025: all tables in the SQL statement must be at the remote database**************
OACNV is the dlink connect from oracle to sql server. Can some one help me as soon as possible
The tables are present in both sql and oracle databases
Edited by: 988802 on Feb 28, 2013 8:23 PM
You do not say which gateway or version you are using but you are most likely hitting this restriction in the 11.2 DG4MSQL and DG4ODBC documentation -
Oracle Database Gateway for SQL Server does not support CALLBACK links. Trying a
CALLBACK link with the gateway will return the following error message:
ORA-02025: All tables in the SQL statement must be at the remote database
You could try rewriting the query using PL/SQL which may avoid the call back.
I tried every way I could but i am unable to rewrite the query can you please help me
SQL> select name from v$database;
SQL> select count(*) from xyz@OACNV
SQL> select count(*) From xyz;
The dblink to sqlserver is working fine no issues with it
Edited by: 988802 on Mar 1, 2013 12:04 PM
Some time ago I connected oracle with sqlserver like you. Every queries worked very slowly. To remedy I created materialized view from remote table (SQlServer) and it was solution. Mayby you can use it.