The column with Numeric datatype has no Length specified (no Precision, no Default, allows NULL). The number datatype without a precision and scale is represented in Oracle by a variable-length numeric with precision of up to 255. There is no SQL Server type that this can be mapped to without loss of precision.
Here are four methods to try to work around the problem:
Set the Length property for NUMERIC column in Oracle table.
If you are not retrieving the NUMERIC column, one workaround is to use OPENQUERY instead of a four-part name in your distributed query.
Use the TO_CHAR function to convert the data in the numeric field to a character value (note that you may have to convert it back for applications that require math operations). For example:
SELECT * FROM OPENQUERY(mylinkedserver, 'select TO_CHAR(F1) from table1')
The best workaround, if possible, is to change the field type in the table to a defined value, such as NUMERIC(15), because many non-Oracle applications have trouble with this data type. However, many servers have corporate restrictions on what can be changed and who can change them. If you can't change the data type on the server, then use one of the other options above.
Microsoft SQL Server version 7.0 provides the ability to perform queries against OLE DB providers. This is done by using the OpenQuery or OpenRowset Transact-SQL functions or by using a query with four-part names including a linked-server name. For example,
sp_addlinkedserver 'mylinkedserver', 'product_name', 'myoledbprovider', 'data_source','location', 'provider_string', 'catalog'
SELECT * FROM OPENQUERY(mylinkedserver, 'select * from table1')
Steps to Reproduce the Behavior
Create a table on Oracle server:
Create table NumTable ( f1 NUMBER )
insert into NumTable values ( 2.3 );
set up a linked server to Oracle server:
EXEC sp_addlinkedserver 'OracleServer', 'Oracle', 'MSDAORA', 'myOracleServer'
EXEC sp_addlinkedsrvlogin 'OracleServer', 'FALSE', NULL, 'demo', 'demo'
Specify a four-part name query:
SELECT * FROM OracleServer..DEMO.NUMTABLE
which will report an error:
Server: Msg 7354, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' supplied invalid metadata for column 'F1'.
The data type is not supported.
Add another column to the table:
Alter table numtable add f2 integer;
Insert into numtable values ( 2.1, 23 );
Specify a distributed query using OpenQuery:
SELECT f2 FROM OPENQUERY(OracleServer, 'SELECT f2 FROM demo.NumTable')
For additional information, please see the following
article in the Microsoft Knowledge Base: