My present article talks about an error that you will encounter when connecting to Oracle database using OPENQUERY. Let us learn about how to fix error 9803.
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 an 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 on 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 in the comment section of this blog. Your suggestions are always welcome!
Reference: Pinal Dave (http://blog.sqlauthority.com)