ODAC OLE DB provider "OraOLEDB.Oracle" for linked server supplied inconsistent metadata for a column

Question

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

Answer 1

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

Answer 2

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.