This is not new guys. It's sad that MIcrosoft and Oracle are well aware of this problem but neither wants to fix it. MS does not have a 64-bit version of MSDAORA and Oracle's OraOLEDB is not friendly with implicit data conversion.
These two companies are playing games at our expense.
We had the same problem, so I checked Metalink and found:
You are running into the following bug: Bug 5011258 SQL SERVER 2005 LINKED SERVER GENERATES INVALID DATA FOR TYPE NUMERIC
Upgrade the Oracle Provider for OLE DB (32-bit or 64-bit) to version 10.2.0.2.20 or higher.
The 10.2.0.2.20 32-bit version is available from metalink.oracle.com as Patch 5473334.
The 10.2.0.2.21 64-bit version for AMD/EM64T and Itanium is available from technet.oracle.com at the following URL:
This worked for us.
We had the problem on a couple win clusters but it worked on a standalone; finally figured out that 1. odac must be in a separate oracle home. 2. set the system environment variable to point to the odac home. The msdaora adapter also worked fine. the win servers are all 32-bit. We'll be uninstalling the clients on all servers and only installing the latest version of odac mentioned in the last post: The 10.2.0.2.20 32-bit version is available from metalink.oracle.com as Patch 5473334.
Now if I could only get oracle stored procs to execute...I'm getting Msg 7357, Level 16, State 2, Line 1
Cannot process the object "exec SS2005USER.O_REFCUR.GETCLASSAD('038308410001',to_date('2008-01-21','yyyy-mm-dd')". The OLE DB provider "OraOLEDB.Oracle" for linked server "MSSP" indicates that either the object has no columns or the current user does not have permissions on that object.
user511308, Peter Obel, mentioned that he used the ROUND() function to get around the invalid data type "numeric" error. Could someone provide an example of how this works when using openquery also? Or, has anyone found another way to massage the data to work with this driver?
SELECT ID, NAME
FROM OPENQUERY(remotedb, 'SELECT round(PERSON_ID,0) as PERSON_ID, NAME FROM infoweb.people') AS derivedtbl_1
I hope this helps, I know it's not optimal, but it does work.
I solved this problem via Metalink DocID 369814.1
In essence the solution is to install an Oracle OLEDB driver with minimum version 10.2.0.2.20.
I installed an Oracle client 10.2.0.3 and then applied the applicable patch (described in the Metalink DocID 369814.1)