1 Reply Latest reply: Jun 10, 2013 4:53 AM by Kgronau-Oracle RSS

    DG4ODBC MSSQL query works in sql, but won't compile in PL/SQL.

    Jim Smith
      I'm using DG4ODBC from 11g to query a SQL Server database. The query I'm using works in a SQL DEveloper SQL worksheet and sqlplus, but won't compile in a PL/SQL procedure.

      The query is
        INSERT
        INTO crm_labels
          (
            accountid,
            label_name,
            cir_labelcode,
            cir_knownasname,
            cir_countryidname,
            parentaccountidname,
            cir_customertypecode1,
            cir_customertypecode2,
            cir_dealstatus
          )
        SELECT "AccountId",
          REPLACE("Name",chr(0)) label_name,
          REPLACE("Cir_labelcode",chr(0)) ,
          REPLACE("Cir_knownasname",chr(0)),
          REPLACE("cir_countryidName",chr(0)),
          REPLACE("ParentAccountIdName",chr(0)),
          "Cir_customertypecode1",
          "Cir_customertypecode2",
          "Cir_dealstatus"
        FROM "dbo"."Account"@crmsvc
        WHERE "Cir_labelcode" IS NOT NULL;
      The error message is
      Error(1): ORA-04052: error occurred when looking up remote object dbo.Account@CRMSVC ORA-01948: identifier's name length (34) exceeds maximum (30)
      I'm guessing that it is attempting to describe additional columns in the Account table.

      If I remove the dbo,
        FROM "Account"@crmsvc
      I get
      Error(1): ORA-04052: error occurred when looking up remote object PUBLIC.Account@CRMSVC ORA-00604: error occurred at recursive SQL level 1 ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [Microsoft][ODBC SQL Server Driver][SQL Serve 
      which is less than helpful. However, if I try to use the same query to create a materialized view, I get.
      SQL Error: ORA-04052: error occurred when looking up remote object PUBLIC.Account@CRMSVC
      ORA-00604: error occurred at recursive SQL level 1
      ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
      [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'Address1_TimeZoneRuleVersionNu'. {42S22,NativeErr = 207}[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'Address1_UTCConversionTimeZone'. {42S22,NativeErr = 207}[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'Address2_TimeZoneRuleVersionNu'. {42S22,NativeErr = 207}[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'Address2_UTCConversionTimeZone'. {42S22,NativeErr = 207}[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'Pias_NewLabelInDealNotificatio'. {42S22,NativeErr = 207}
      ORA-02063: preceding 2 lines from CRMSVC
      which seems to confim that in some circumstance, oracle will attempt to parse more than was asked for.

      Any work arounds?

      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      PL/SQL Release 11.2.0.3.0 - Production
      CORE 11.2.0.3.0 Production
      TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
      NLSRTL Version 11.2.0.3.0 - Production

      DG4ODBC is also 11.2.0.3
        • 1. Re: DG4ODBC MSSQL query works in sql, but won't compile in PL/SQL.
          Kgronau-Oracle

          The gateway claims about this column "Address1_TimeZoneRuleVersionNu" which is not listed in your select list.

          When a gateway connection is established it first checks out all columns of the table you want to select to determine the data types and then it fetches the data for the columns you want to select. But there's also an exception - when you use functions the gateway is not able to translate into the syntax of the foreign database. in this case ALL columns will be fetched into the Oracle database and the result is processed locally (post processing).

           

          You're using replace function which won't be mapped to the foreign database equivalent using DG4ODBC so it will post process the result and fetch from all columns all the data into the Oracle database. Your table contains a column which exceeds the 30 character limitation of Oracle, hence the select will fail.

          The only work around is to create a view on the foreign database side which reduces the column name length to 30 characters or less.