How to connect to Oracle Database from SQL Server instance and query the Oracle Database?

 

How to connect to Oracle Database from SQL Server instance and query the Oracle Database?

Linked Server is the solution for this.
A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources. After a linked server is created, distributed queries can be run against this server, and queries can join tables from more than one data source. If the linked server is defined as an instance of SQL Server, remote stored procedures can be executed.

A linked server definition specifies the following objects:

· An OLE DB provider : is a DLL that manages and interacts with a specific data source

· An OLE DB data source : identifies the specific database that can be accessed through OLE DB

Basics of Linked Server Configuration:

Advantages of Linked Server:

· The ability to access data from outside of SQL Server.

· The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.

· The ability to address diverse data sources similarly.

Pre-requisite

· Install Oracle Client

· Configure tnsnames.ora

Example

STG21.SIVA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oraDBsrvr.siva.com)(PORT = 60000)) ) (CONNECT_DATA = (SERVICE_NAME = stg01.siva) )

Validate tnsnames.ora entry

On Command Prompt Tnsping STG21.SIVA

Notes:

Here we are going to use Microsoft ODBC for Oracle as the OLE DB Provider.
The STG21.SIVA is going to be OLE DB Data Source pointing to the Oracle Database STG01.SIVA

Open up ODBC

Select System tab

Click Add

Select Microsoft ODBC for Oracle

Click Finish

Click OK, OK

Connect to SQL Server Management Studio

Right click select new Linked Server

Click OK

Test Connection

Right click select Test connection

Validation:

Just run the sample query

exec ('select user, sysdate from dual') at STG21_SQLSVR54

Output look like;

USER SYSDATE SQLSVR07 2012-09-25 01:37:02.000