Invalid Data for type "numeric"

Question

Invalid Data for type "numeric"

Paul Bukowski

Mr or Mrs. 500

Points: 565
More actions
July 30, 2009 at 9:42 am

#208803

Hi,
I'm curious if this issue ever got resolved? I saw many posts on the internet about the exact same problem many years ago, but nothing recent. The problem has to do with columns of Oracle data set as NUMERIC. We can do Round() and this will work but is painful for lots of columns.
The below query should run ok, but does not. It is performing the query from our 64 bit sql server 2005 against a linked Oracle server. using the Oracle Provider for OLE DB native driver.
SELECT * FROM OPENQUERY(RXR,'
SELECT
AL1.SM_ID,
AL1.MINUTES,
AL1.SECONDS,
AL1.RES_TOTAL_SUBS,
AL1.COMM_VIDEO_SUBS,
AL1.COMM_TEL_SUBS,
AL1.COMM_HSD_SUBS,
AL1.RES_VIDEO_SUBS,
AL1.RES_HSD_SUBS,
AL1.RES_TEL_SUBS
FROM AR.MAINTENANCE AL1
')
Here is the error message received:
.Net SqlClient Data Provider: Msg 9803, Level 16, State 1, Line 1
Invalid data for type "numeric".
I’ve done a bunch of research and many, many users have the exact same problem. There are only 2 solutions I’ve been able to find:
1)Add some additional sql and perform a Round() or Convert() on any numeric data (this works but is a terrible pain in the butt)
2)Upgrade to this Oracle driver http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html
Here is 1 thread on the subject:
Google this:
sql server 2005 oracle invalid data for type numeric
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/aab84922-0375-41a5-a486-8301c93c8ccd
Here’s the possible solution at the bottom:
Make sure you are using ODAC 64 bit 10.2.0.3 at the very least. This will resolve the Msg 9803 numeric errors.
http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html
http://forums.oracle.com/forums/thread.jspa?messageID=2846544
Has anyone upgraded to this driver? We are still using the 10.1.x driver
Does anyone know if a Microsoft OLE 64 bit driver is available for Oracle and have you tried it?
Thanks,
Paul

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Paul Bukowski Mr or Mrs. 500 Points: 565 More actions · Answer 1

Apparently this was a bug in the Oracle driver. The solution is to install an updated driver. Here are the details in case anyone else has same issue:

http://forums.oracle.com/forums/thread.jspa?threadID=337842&start=15&tstart=-2

Thanks,

Paul

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 2

Since there is no NUMERIC data type in Oracle - closest one would be NUMBER I think driver was attempting some kind of data conversion from default datatype and failed.

Probably new version of the driver is smarter and got it done.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

chandan_jha18 SSChampion Points: 11330 More actions · Answer 3

http://www.microsoft.com/downloads/details.aspx?FamilyId=D9CB21FE-32E9-4D34-A381-6F9231D84F1E&displaylang=en#filelist

Try if you find something.I always swap between the two drivers (MSDAORA and ORAOledb)and one of them always helped me in many cases:-)

sreddyv26 Ten Centuries Points: 1038 More actions · Answer 4

Helo paul

In your query try with to_number(NumCol)

example

exec('select to_number(empno),ename from emp') at oralinksrv

Regards

Reddy