SQL Server 2019 using Linked server to Oracle 19c (19.9) using Oracle 19c client on Windows - DBCOLUMNFLAGS_ISNULLABLE error

Question

Having issues selected data for some Oracle tables over the linked server from SQL Server:

select type, count(*) from RMTDB..JOB1308.HOST_INTERFACE group by type order by 1 desc;

Msg 7356, Level 16, State 1, Line 13 The OLE DB provider "OraOLEDB.Oracle" for linked server "RMTDB" supplied inconsistent metadata for a column. The column "CREATE_TIME" (compile-time ordinal 3) of object ""JOB1308"."HOST_INTERFACE"" was reported to have a "DBCOLUMNFLAGS_ISNULLABLE" of 0 at compile time and 32 at run time.

Open query version of it works:

select * from OPENQUERY( RMTDB, 'select type, count(*) from HOST_INTERFACE group by type order by 1 desc')

result

TYPE COUNT(*)
10 10450
8 6543
6 21

Anyone run into this?

• David Browne - Microsoft: What is the definition of HOST_INTERFACE in Oracle? What version of Oracle's OleDB provider are you using?
• sherveyj: I am using Oracle 19c windows client - freshly downloaded.
• sherveyj: SQL> describe host_interface Name Null? Type ---------------- -------- ------------------------------------------------- MESSAGE_ID NUMBER(9) TYPE NUMBER(8) CREATE_TIME TIMESTAMP(6) WITH LOCAL TIME ZONE READ_TIME TIMESTAMP(6) WITH LOCAL TIME ZONE STATUS NUMBER(9) STATUS_DETAILS VARCHAR2(1000) RECEIVER NUMBER(9) PAYLOAD CLOB
• sherveyj: This view works ok: SQL> describe V_INVENTORYSTACKS; Name Null? Type --------------------- -------- ------- STACK NOT NULL NUMBER(3) ROBOT_ZONE NOT NULL NUMBER(2) SKU NOT NULL NUMBER(12) SKU_DESCRIPTION VARCHAR2(40) STATE VARCHAR2(8) LOCK_BOTTOM_LAYER NOT NULL NUMBER(1) REQUEST NOT NULL NUMBER(7) LAYERS NOT NULL NUMBER(2) LOT VARCHAR2(10) MEASURE NOT NULL NUMBER(1) EMPTY NOT NULL NUMBER(1)

Answer 1

I found a support.oracle.com Doc ID 1519958.1 that describes the issue.
For solution it says: Use the relatively simple work around of linking to a view that is based upon the table, instead of directly to the table.

Apparently this issue has been around since 2012.

Answer 2

this still seems to be an issue with 2019. In my case I am able to query just the oracle table via openquery but was not able to join from sql server table to the openquery without getting this error. Also, 4-part qualifier syntax (alternative to openquery) gives error as well. Even just querying the oracle table by itself.

A simple solution I found is to rearrange the query putting the openquery first in the from clause and joining from it to the sql server table.