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”.
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)
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.
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/
@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.
@ Imran Mohammed very true.
As always your comment is really adding value to original article.
Kind Regards,
Pinal
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
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
and, change the ‘dual’ with your tablename :) (***forgot to mention that)
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.