4

I am using SQL Server 2014 linked server to connect to an Oracle 11gR2 (11.2.0.3) database, using the ODAC 12.1.0.2.4 Oracle OLE DB Provider. I have a stored procedure that lives in a SQL 2014 database and inserts data from a local table into a remote Oracle table via the linked server, using the following syntax:

insert into LinkedServerName..RemoteSchema.RemoteTable(column) 
select column from localSQLServerTable

However, I receive the error below on the line of the insert:

Msg 7356, Level 16, State 1, Procedure {MyProcName}, Line 1 The OLE DB provider "OraOLEDB.Oracle" for linked server "{MyLinkedServer}" supplied inconsistent metadata for a column. The column "{columnName}" (compile-time ordinal 23) of object "{remote table name}" was reported to have a "DBCOLUMNFLAGS_ISFIXEDLENGTH" of 16 at compile time and 0 at run time.

The column in question is defined in Oracle as CHAR(1 BYTE) data type.

Installed "64-bit ODAC 12c Release 4 (12.1.0.2.4) Xcopy for Windows x64", download name "ODAC121024Xcopy_x64.zip" from http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

4

Even though Oracle support note 207303.1 states that the 12c client works with 11gR2 client, this error seems to be caused by a compatibility issue between the 2 different versions. To fix, I followed these steps:

  1. Uninstall the 12c ODAC version, by opening an Admin command prompt, changing directories to the installed Oracle home folder, and running: uninstall.bat all d:\oracle, where d:\oracle is my Oracle home location.
  2. Remove the PATH environment variables
  3. Reboot
  4. Install the 11.2.0.4 ODAC version "64-bit ODAC 11.2 Release 6 (11.2.0.4.0) Xcopy for Windows x64", download the "ODAC112040Xcopy_64bit.zip" option. Make sure you install via Admin command prompt (the install command is: install.bat all d:\oracle11gr2 odac11gr2). Download from: http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html
  5. Add back the PATH environment variables for d:\oracle11gr2;d:\oracle11gr2\bin
  6. Reboot again
|improve this answer|||||
  • We are using Oracle 10g here, I can confirm downgrading from ODAC 12c to 11.2 works... – Rosdi Kasim Nov 16 '17 at 6:56
3

From SQL Server Management Studio (SQL Server 2012 instance), I have created a linked server to an Oracle 12c database using the 12c ODAC Provider.

I have run across "The OLE DB provider "OraOLEDB.Oracle" for linked server [???] supplied inconsistent metadata for a column..." errors while simply trying to query tables from the Oracle database, in this manner SELECT * FROM [LINKEDSERVER]..[SCHEMA].[TABLE]

I have tried installing the version 11 ODAC provider, but continued to get the same results.

What has worked for me has been to read the error message carefully to find out what column(s) are causing the error, and then to CAST or change those columns to a different data type on the Oracle side. One time I had to change the columns from NUMBER(?,0) to either INT or DECIMAL(?,X) where X was greater than zero. Another time I had to change CHAR(6) to VARCHAR(6) to get it to come across.

Others have success using OPENQUERY as a workaround, which may be our only option if we don't have the ability to tweak column data types Oracle-side.

I'm sure there's someone out there who has the technical expertise to dig deeper into this issue, but my feeling is that the fault lies in the interplay between SQL Server and Oracle's data provider. Oracle and Microsoft aren't exactly friendly with each other, or prone to do each other a favor for the benefits of us users.

|improve this answer|||||
  • 1
    We have run into issues where we did have to cast columns as another data type in order for the data type mapping from Oracle to SQL server to work, especially for types declared as numeric without a specified precision. In this example, it was integration with Oracle e-business suite and was an insert into Oracle table so we didn't have the option of changing the table structure. I recommend using openquery for most queries against a live Oracle database bc otherwise your query filters are applied on the SQL server side instead of Oracle side, but openquery won't work with insert statements. – BateTech Aug 27 '16 at 13:11
  • Just to add this: You can use an insert statement with openquery according to Microsoft. docs.microsoft.com/en-us/sql/t-sql/functions/… – Konrad Mar 12 '19 at 7:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.