4 Replies Latest reply: Jan 28, 2013 12:27 AM by Kgronau-Oracle RSS

    Oracle to SQL Server database link, can only use dynamic SQL

    987199
      I have a database link created from Oracle (version 10.2.0) to SQL Server. The database link works. I can query the SQL Server database directly, ex SELECT columna FROM databasename@LINK;

      When I create a procedure/function/package in Oracle though and try the same query - in a cursor or a SELECT INTO, I get a ORA-00942, table or view does not exist error upon compiling the package.

      If I put that same select statement into a string and do an EXECUTE IMMEDIATE as dynamic SQL though, it works and everything is fine.

      Any ideas?
        • 1. Re: Oracle to SQL Server database link, can only use dynamic SQL
          Mkirtley-Oracle
          Hi,
          Can you give us an example of the code that causes the ORA-942 error ? SQL*Server can be case sensitive for object names so it depends how they are given in the procedure.
          Also, any privileges need to be granted directly in PL/SQL and not via a role so make sure that isn't a possible cause.

          Regards,
          Mike
          • 2. Re: Oracle to SQL Server database link, can only use dynamic SQL
            987199
            I tried the case sensitivity also, but no luck there.

            PROCEDURE prcJentest IS

            CURSOR c1 IS
            SELECT co.id
            FROM dbo.customer_order@wce06 co;

            BEGIN
            null;
            END prcJentest;



            I've also tried the above with "dbo"."customer_order"@wce06 and that doesn't work either.
            • 3. Re: Oracle to SQL Server database link, can only use dynamic SQL
              Solomon Yakobson
              984196 wrote:
              I have a database link created from Oracle (version 10.2.0) to SQL Server. The database link works. I can query the SQL Server database directly, ex SELECT columna FROM databasename@LINK;
              Are you saying you can issue in Oracle SQL*Plus:
              SELECT co.id
              FROM dbo.customer_order@wce06 co;
              And it works. Then it should not give you ORA-00942. Keep in mind, Oracle names are case sensitive, so you must use Oracle quoted names. Table and column names must be types in EXACT case they show up in SQL Server enclosed in double quotes. So post SQL*PLus execution (along with results) snippet of:
              SELECT co.id
              FROM dbo.customer_order@wce06 co
              WHERE rownum = 1;
              SY.
              • 4. Re: Oracle to SQL Server database link, can only use dynamic SQL
                Kgronau-Oracle
                Please pay attention that you also have to surround column names by double quotes as they are case sensitive as well.

                So just to clarify when you use SQL*Plus then this select works:
                SELECT co."id" FROM "dbo"."customer_order"@wce06 co;

                but when you use it in a cursor then it fails with ORA-942 error. Could you please provide the source table definition and a gateway trace level DEBUG. Gateway tracing is enabled in the gateway init file by setting HS_FDS_TRACE_LEVEL=DEBUG - then starting a new SQL*Plus session and executing the code. Once finished, exit from the SQL*Plus session and disable tracing again. The trace file is now located in the <gateway>/log or <gateway>/trace directory depending on the platform.

                - Klaus