Query Oracle from SQL Server

Start with the following steps:

• Install the Oracle Instant Client (OIC) on the machine where SQL Server is running and add the "Oracle Provider for OLE DB" driver, e.g. to folder c:\oracle11g.
• For a SQL Server x64 version running on a Windows x64 OS download the instantclient-basiclite-windows.x64-11.2.0.2.0.zip. This is a 22 MB download.
• Then add the Oracle Data Access components, to get the "OLE DB for Oracle" driver:  http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html
  At the time of writing (March 2012): select the "64-bit ODAC 11.2 Release 4 (11.2.0.3.0) Xcopy for Windows x64". This is a 54 MB download
• Inside SQL Server Management Studio:
  Linked Servers -> Providers -> OraOLEDB.Oracle -> Allow inprocess = true
• Optional: to enable adhoc querying (querying without a linked server), then enable this:
• SQL Server 2005 via surface area configuration tool
• SQL Server 2008 via facets: inside SQL Server Management Studio, right click the server -> facets -> facet: surface area configuration -> AdHocRemoteQueries = true
• Optional: if the connection to Oracle should be based on a TNS names entry, then add an "admin" folder to the OIC folder. Put or create an tnsnames.ora file there, e.g. c:\oracle11g\admin\tnsnames.ora
• Optional: use "tnsping" to test the entry from the command prompt, e.g. "tnsping dwh.world". If it doesn't recognize the entry, then create an environment variable called "TNS_ADMIN" and let it point to this "admin" folder, e.g. c:\oracle11g\admin
• Optional: if the following steps to get data from an Oracle instance don't work, then edit this registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI.
  Set the following values:
  OracleXaLib=oraclient11.dll
  OracleSqlLib=orasql11.dll
  OracleOciLib=oci.dll

Note: these DDLs can be found in the c:\oracle11g\product\11.2.0\client_1\BIN folder. The specific version depends on the Oracle Client and might be for example oraclient10.dll en orasql10.dll. Use the names of the DDLs as found in this folder.

Three ways to get the data from an Oracle instance

Example:

Server: locksnlatches
Oracle instance (SID): dwh
Port: 1521
Schema/user: scott
Password: tiger
tnsnames.ora entry:

dwh.world =
(DESCRIPTION=
  (CID=DWH_APP)
  (ADDRESS_LIST=
    (ADDRESS=
      (PROTOCOL=TCP)(HOST=locksnlatches)(PORT=1521)
     )
   )
  (CONNECT_DATA=
    (SID=dwh)
  )
)

1) Using a linked server

• Linked Servers -> New linked server... ->  General ->
• linked server: lnk_locksnlatches (this name can be anything)
• provider: Oracle provider for OLE DB
• product name: oracle (this name can be anything)
• data source: (DESCRIPTION=(CID=DWH_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=locksnlatches)(PORT=1521)))(CONNECT_DATA=(SID=dwh)))
• provider string: leave empty!
• location: leave empty!
• catalog: leave empty!
• Linked Servers -> New linked server... ->  Security ->
• Be made using this security context
• Remote login: scott
• With password: tiger

Use the four-point notation to retrieve data from Oracle:

SELECT 1 FROM lnk_locksnlatches..scott.dual

Note: the "security" tab specifies the Oracle schema/user and password used by the connection!

2) Without a linked server (TNS-less)

SELECT * FROM OPENROWSET('OraOLEDB.Oracle', '(DESCRIPTION=(CID=DWH_APP)(ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=locksnlatches)(PORT=1521)))(CONNECT_DATA=(SID=dwh)))';'scott';'tiger','SELECT 1 FROM DUAL') x

3) Without a linked server (TNS-full)

SELECT * FROM OPENROWSET('OraOLEDB.Oracle', 'dwh.world';'scott';'tiger','SELECT 1 FROM DUAL') x