PRB: SQL Distributed Query with Oracle NUMERIC Column Gives Error
This article was previously published under Q221552
This article has been archived. It is offered "as is" and will no longer be updated.
Attempting to use an Oracle linked server using the MSDAORA OLE DB provider, which queries a table with a NUMERIC column, may give an error similar to the following:
Server: Msg 7354, Level 16, State 1, Line 1OLE DB provider 'MSDAORA' supplied invalid metadata for column 'columnname'. The data type is not supported.The query may fail also if the Numeric column is not in the select list.
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'goEXEC sp_addlinkedsrvlogin 'OracleServer', 'FALSE', NULL, 'demo', 'demo'go
- Specify a four-part name query:
SELECT * FROM OracleServer..DEMO.NUMTABLEwhich 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')
220915 PRB: SQL Distributed Query with Oracle Needs Oracle Client
Article ID: 221552 - Last Review: 12/05/2015 13:15:54 - Revision: 3.1
Microsoft SQL Server 7.0 Standard Edition, Microsoft OLE DB Provider for Oracle Server 1.0, Microsoft OLE DB Provider for Oracle Server 1.0, Microsoft OLE DB Provider for Oracle Server 1.0, Microsoft OLE DB Provider for Oracle Server 1.0
- kbnosurvey kbarchive kbdatabase kboracle kbprb kbprovider KB221552