Have a application/procedure in SQL Server which needs to connect to Oracle Database and check some data.
Based on the data I will process in SQL Server.
How do we connect from SQL Server to Oracle and return back values..?
I think your answer is in below two links :
In addition to Girish, please see this link PS:Please dont forget to change thread status to answered if it possible when u belive your thread has been answered, it pretend to lose time of other forums user while they are searching open question which is not answered,thanks for understanding
I commonly do not recommend 3rd party web sites that show how to connect from Oracle to a foreign database. Most of them are outdated or contain wrong configuration files. Best would be if you have access to the Oracle support site "My Oracle Support" http://support.oracle.com to check out the Gateway Master note:
Master Note for Oracle Gateway Products (Doc ID 1083703.1)
It lists the option Oracle offers to you how 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 the differences in the functionality and licensing of the Database Gateway for ODBC and the other gateways which are discussed in these notes - Document 252364.1 Functional Differences Between Generic Connectivity and Database Gateways Document 232482.1 Gateway and Generic Connectivity Licensing Considerations
Finally also links to the available configuration notes: Document 437374.1 How to Configure DG4MSQL (Oracle Database Gateway for MS SQL Server) Release 11 on Linux x86 32bit post install Document 466267.1 How to Configure DG4MSQL (Database Gateway for MS SQL Server) on Windows 32bit post install Document 562509.1 How to Configure DG4MSQL (Oracle Database Gateway for MS SQL Server) 64bit Unix OS (Linux, Solaris, AIX,HP-UX) post install Document 1086365.1 How to Configure DG4MSQL (Database Gateway for MS SQL Server) on a 64bit Windows post install Document 466228.1 How to Configure DG4ODBC on Linux x86 32bit to Connect to Non-Oracle Databases post install Document 561033.1 How to Configure DG4ODBC on 64bit Unix OS (Linux, Solaris, AIX, HP-UX) to Connect to Non-Oracle Databases Post Install Document 466225.1 How to Configure DG4ODBC (Oracle Database Gateway for ODBC) on Windows 32bit to Connect to Non-Oracle Databases Post Install Document 1266572.1 How to Configure DG4ODBC (Oracle Database Gateway for ODBC) on 64bit Windows Operating Systems to Connect to Non-Oracle Databases Post Install
Unfortunately it does not cover foreign options like for example the MS SQL Server Linked/shared server mechanism which allows you to connect from a SQL Server to the Oracle database.