My requirement is to connect SQL server 2008 R2 64bit to oracle 10.2.0.4.0. and get the data from the SQL database. The SQL server database is located in a remote server. Please let me know the most suitable method to connect.
Oracle database is running in Red hat Linux 4 enterprise edition 32bit version.
I assume that your Oracle database should be the driving site that pushes and pulls the data from/to the SQL Server.
Here Oracle offers 2 different products, a Database Gateway for ODBC and a Database Gateway for MS SQl Server which both give you the opportunity to define a database link to a MS SQL Server and to integrate it into the Oracle database.
More details can be found on the My Oracle Support portal (support.oracle.com), then check out the Gateway Master Note:
Master Note for Oracle Gateway Products (Doc ID 1083703.1)
The note contains links to other content which gives you an overview about the options you have to connect to a foreign database: Document 233876.1 Options for Connecting to Foreign Data Stores and Non-Oracle Databases - For example - DB2, SQL*Server, Sybase, Informix, Teradata, MySQL
and it also explains the functional and licensing differences of the Database Gateway for ODBC and the other gateways: Document 252364.1 Functional Differences Between Generic Connectivity and Database Gateways Document 232482.1 Gateway and Generic Connectivity Licensing Considerations
Once you decided which gateway you want to use you can also find the configuration notes linked to the Gateway Master Note.
Edited by: kgronau on Dec 11, 2012 9:53 AM
Maybe just some words about the versions:
Your Oracle database release 10.2.0.4 is certified with the currently latest Oracle database Gateway release 184.108.40.206 (DG4ODBC and also DG4MSQL). When you decide to use DG4MSQL, then the 220.127.116.11 release is also certified for SQL Server 2008, when you plan to use DG4ODBC you need to find a suitable 3rd party ODBC driver that is certified with MS SQl Server 2008.