I'm setting up linked server from sql server to oracle. I installed Oracle client 10.2.0.1 on the server, configured ODBC to the Oracle database using OraOLEDB.Oracle provider. Created linked server on sql server, but getting the below error:

tnsping to the oracle database works from the server and the ODBC connection testlink to the oracle database is successful. I tried checking Allow inprocess in the provider option by right clicking linked server properties on the management studio also.. Nothing seemed to work..

Thanks Alex. I installed Oracle client 10.2.0.1, created linked server and followed the steps above and it worked. But get the following error for some selects..

SELECT * FROM
testlink..user.table

Invalid data for type "numeric".

I researched on this error that it is fixed in ODAC 10.2.0.2.21. I first installed Oracle client (Oracle10g_client_64bit.zip) and then tried installing ODAC (Oracle 10g Release 2 ODAC 10.2.0.2.21) but get a "runtime error" during the install.. Not sure if I'm missing something...

Hi,
Whenever I detect strange behavior of an Oracle installation I first check the paths:
Installation folder and destination paths should not have blanks or empty space characters.
I can tell you for 11g x64 client installation. It does not work if the installation folder contains blank.

Do you have only problem with numeric tables? How about tables with string values only?

You get this error because the Oracle data dictionary is case sensitive. Oracle object names are all uppercase by default. The linked-server query must exactly match the case of all referenced Oracle metadata, such as schema and table names. So, the following query works:
SELECT * FROM oradb..SCOTT.DEPT WHERE deptno = 10