Why Does SQL Server OPENQUERY To Oracle Return Different Column Types On SQL Server 2016 to 2008

Question

I have two SQL Server Instances on the same machine, one 2008 and one 2016. Both instances have a linked server connection to the same Oracle 12 Server.

If I run the following query 2008 returns 1.23 as a VARCHAR but 2016 returns it as a float

SELECT * INTO #tst FROM OPENQUERY(JOHPROD,'SELECT 1.23 FROM dual')
SELECT t.Name,* FROM tempdb.sys.columns c
    INNER JOIN sys.types t ON t.system_type_id = c.system_type_id
WHERE [object_id] = OBJECT_ID(N'tempdb..#tst');

Both instances are pointing to the same Oracle server and both using the same Oracle ODBC driver.

Both linked servers have this same definition...

EXEC master.dbo.sp_addlinkedserver @server = N'ORAPROD', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'ORAPROD'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ORAPROD',@useself=N'False',@locallogin=NULL,@rmtuser=N'ORAUSER',@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'collation name', @optvalue=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

Any idea why these would return different types?

Answer 1

This behaviour has changed in recent updates. See FIX: The value of NUMBER type is truncated when you select data from an Oracle-linked server by using OLE DB provider

After you apply this fix, NUMBER values with unknown precision/scale are treated as double values with OLE DB provider. If the precision is important and the range of the values is not large enough, you can enable the new Trace Flag 7314 that would start treating such values as numeric (38, 10).

However I'm a bit surprised you are getting a float instead of a double \ numeric since that seems to contradict the article somewhat, so I'm not sure it explains your exact situation.