PRB: SQL Distributed Query with Oracle NUMERIC Column Gives Error

• Microsoft SQL Server version 7.0
• Microsoft OLE DB Provider for Oracle, version 2.0

SYMPTOMS

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 1 OLE DB provider 'MSDAORA' supplied invalid metadata for column 'columnname'. The data type is not supported.

CAUSE

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.

WORKAROUND

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.

MORE INFORMATION

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

1. Create a table on Oracle server:




Create table NumTable ( f1 NUMBER )

insert into NumTable values ( 2.3 ); 

2. set up a linked server to Oracle server:
   

   
   EXEC sp_addlinkedserver   'OracleServer',  'Oracle',  'MSDAORA',  'myOracleServer'
   
   go
   
   EXEC sp_addlinkedsrvlogin 'OracleServer', 'FALSE', NULL, 'demo', 'demo'
   
   go 




3. 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.




4. Add another column to the table:
   

   
   Alter table numtable add f2 integer;
   
   Insert into numtable values ( 2.1, 23 ); 




5. Specify a distributed query using OpenQuery:
   

   
   SELECT f2 FROM OPENQUERY(OracleServer, 'SELECT f2 FROM demo.NumTable') 

Q220915 PRB: SQL Distributed Query with Oracle Needs Oracle Client

Additional query words:

Keywords : kbDatabase kbOLEDB kbSQLServ kbVC
Version : WINDOWS:2.0; winnt:7.0
Platform : WINDOWS winnt
Issue type : kbprb