SQL SERVER – Fix Error 9803. Invalid data for type “numeric” – Data Type Mapping

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.

SQL SERVER - Fix Error 9803. Invalid data for type "numeric" - Data Type Mapping errorstop The error that eventuates is as follows:

Msg 9803, Level 16, State 1, Line 1
Invalid data for type “numeric”.

Fix/Solution/Workaround:

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 (https://blog.sqlauthority.com)

, , , ,
Previous Post
SQL SERVER – Maximum Columns per Primary Key – Fix : Error : Msg 1904, Level 16, The index on table has column names in index key list. The maximum limit for index or statistics key column list is 16
Next Post
SQLAuthority News – Download SQL Server 2005 Report Packs – SQL Server Sample Reports – Report Templates

Related Posts

8 Comments. Leave new

  • Nice, I wish you had published this a couple weeks ago before I ran into this problem in an SSIS package :)

    On Oracle 9i we had the same issue — and came up with the same solution (after we tried every single numeric data type Oracle has). You might be able to work around by using Oracle’s ROUND() function also, but I gave up messing with it because of the deadline.

    Another issue we have with SSIS packages connecting to Oracle 10g is the odd Oracle NUMBER(*) and NUMBER(*, n) syntax. SSIS doesn’t recognize it. My solution is to explicitly cast the columns to DECIMAL(x, y) type in the Oracle source query.

    Reply
  • Thank you Michael,

    For sharing your real life scenario.

    Michael Coles is author of many SQL Server related books : https://blog.sqlauthority.com/2009/04/20/sqlauthority-book-review-pro-t-sql-2008-programmer’s-guide-by-michael-coles/

    Reply
  • Imran Mohammed
    May 5, 2009 9:05 am

    @Pinal

    Another observation is, When you use OpenQuery, you can actually write PL-SQL Script in select statement. Like you did.

    TO_CHAR is an PL-SQL Function.

    Like you said, this error mainly occurs because of Numeric Precision. Even if you do not use To_Char function, Query should still work.

    In short, if you use OPenQuery then you need not use TO_CHAR function (I am talking very specific to the given error).

    ~ IM.

    Reply
  • how would I fix the phone #s to remove and display this kind of format?
    (xxx)xxx-xxx
    instead of right now the format is
    xxx/xxx-xxx or
    xxx/xxxxxx

    Reply
    • Hi I am new to oracle…I believe you got the solution but trying to answer

      select replace(concat(‘(‘, concat(substr(replace(replace(‘123/456-7890’, ‘/’, ”), ‘-‘, ”), 1, 3), (concat(‘)’, substr(to_char(replace(replace(‘123/456-7890’, ‘/’, ”), ‘-‘, ”), ‘999999G9999’), 4, 8))))), ‘,’, ‘-‘) phonenumber from dual

      where ‘123/456-7890’ or ‘123/4567890’ (please replace the number with the fieldname) is in the current format…

      The output would be (xxx)xxx-xxx

      Reply
  • Hi
    when i run this query…
    select * from openquery(ORACLELINKEDSERVER,’select * from VIEW_STUDENT’)

    I got this error…..
    Msg 7356, Level 16, State 1, Line 1
    The OLE DB provider “OraOLEDB.Oracle” for linked server “ORACLELINKEDSERVER” supplied inconsistent metadata for a column.
    The column “SID” (compile-time ordinal 1) of object “select * from STUDENT” was reported to have a “DBTYPE” of 130 at compile time and 129 at run time.

    can any one have solution pls.

    Reply

Leave a Reply

Menu