SQL SERVER – Fix : Error : Msg 9803, Level 16. Invalid data for type “numeric” – Data Type Mapping for Oracle Publishers

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

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 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)

8 thoughts on “SQL SERVER – Fix : Error : Msg 9803, Level 16. Invalid data for type “numeric” – Data Type Mapping for Oracle Publishers

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

    Like

  2. @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.

    Like

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

    Like

    • 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

      Like

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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s