Access Oracle tables in SQL Server 2005

In a heterogeneous environment it sometimes becomes necessary to access information across different database. In this exercise I will demonstrate how you can access Oracle db tables in SQL Server 2005.
1. First you need to install Oracle client on the SQL server 2005 server. You can install using the instant client option. I am using Oracle 11gR2 client.
2.  Download ODAC (Oracle Data Access Components from the Oracle website depending on your server platform 32-bit or 64-bit.
http://www.oracle.com/technetwork/database/windows/downloads/utilsoft-087491.html
3. Edit TNSNAMES.ora

ORACLELS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = SERVERNAME)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = FRIENDLYNAME)
    )
  )

There are a couple of things you need to change:

• HOST = SERVERNAME. The value SERVERNAME should be changed to reflect the actual address or hostname of the target system.
• SERVICE_NAME = FRIENDLYNAME. FRIENDLYNAME is the name by which you refer to this actual connection.

4.  REBOOT
5. Configure provider in SQL Server

1. 1. Databases→DBName→Server Objects→Linked Servers→Providers→OraOLEDB.Oracle→Properties→Enable “Allow inprocess”

   

2. Create a linked server to the Oracle Database
   1. General
      1. Linked Server: A name of your choosing which you will use when querying using four-part naming conventions.
      2. Provider: Oracle Provider for OLE DB
      3. Product Name: “Oracle” is fine here
      4. Data Source: This should match the tnsnames.ora entry. For example in our case we will use ‘ORACLELS’ as the data source.
      Create a Linked Server
   2. Security
      1. Select Be made using this security context and supply the remote login and password.
3. Query the linked server:  select * from openquery(oraclels, ‘select * from db_name.table_name where <clause> ‘)

The syntax for Insert and Update is :
INSERT OPENQUERY (linkedserver, ‘SELECT fieldlist FROM tablename‘) VALUES (valuelist);
UPDATE OPENQUERY (linkedserver, ‘SELECT * FROM tablename WHERE field = ”value”) SET list(field=value);
SELECT * from OPENQUERY(linkedserver,’SELECT COUNT(*) FROM tablename WHERE field = ”value”‘).
Simple and Easy. Now you can access  Oracle tables and run Oracle statements inside from SQL Server 2005. The other way around when you need to access SQL Server tables from Oracle is a little tricky. You need to use something called as heterogeneous services. I will post another article showing, how to do this using a Windows server and then using a Unix server. For Unix server you need ODBC drivers which is generally 3rd party tools. Since ODBC is not native to Unix/Linux.  Keep watching this space for more 🙂