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
Best Answer
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:
uninstall.bat all d:\oracle, where d:\oracle is my Oracle home location.install.bat all d:\oracle11gr2 odac11gr2). Download from: http://www.oracle.com/technetwork/database/windows/downloads/index-090165.htmld:\oracle11gr2;d:\oracle11gr2\bin