Oracle Database Discussions

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