My present article talks about an error that you will encounter when connecting to Oracle database using OPENQUERY.
The error that eventuates is as follows:
Msg 9803, Level 16, State 1, Line 1
Invalid data for type “numeric”.
As far as I can discern, the above error occurs due to numeric precision or numeric definition mismatch. The number field of SQL Server does not appropriately match with the number field of Oracle. In fact, apart from number field there are several other data types that do not match.
I am including the following sample query having NumberCol that is Integer field of SQL Server and it needs to be converted To_Char to match up. NumberCol can be matched to Numeric in Oracle as well.
SELECT CONVERT(INT, NumberCol) AS NumberCol
FROM OPENQUERY (YourConnectionMethod, 'SELECT TO_CHAR(NumberCol) AS NumberCol FROM RemoteTbl');
Do read the article of MSDN to have a sound knowledge about Data Type Mapping for Oracle Publishers.
Please drop a line to me and let me have your doubts and questions. Your suggestions are always welcome!
Reference : Pinal Dave (http://blog.sqlauthority.com)