3 Replies Latest reply: Mar 1, 2013 6:43 PM by AndyPol RSS

    Issue with Oracle 11g and Sqlserver

    991805
      Hi,

      Below is the query I am trying to run

      select T.*
      from xyzl@OACNV T
      where 1=1
      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
        • 1. Re: Issue with Oracle 11g and Sqlserver
          Mkirtley-Oracle
          Hi,
          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 -

          CALLBACK links
          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.

          Regards,
          Mike
          • 2. Re: Issue with Oracle 11g and Sqlserver
            991805
            Hi Mike,

            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;

            NAME
            ---------
            DEV

            SQL> select count(*) from xyz@OACNV
            2 ;

            COUNT(*)
            ----------
            118

            SQL> select count(*) From xyz;

            COUNT(*)
            ----------
            321

            The dblink to sqlserver is working fine no issues with it

            Edited by: 988802 on Mar 1, 2013 12:04 PM
            • 3. Re: Issue with Oracle 11g and Sqlserver
              AndyPol
              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.