Establishing a Connection from Oracle to SQL Server

Here are steps to make a connection from Oracle to SQL Server using Oracles' heterogeneous Services ODBC agent.

Oracle has a generic connectivity methodology that allows for the Oracle database server to access non-Oracle database systems through ODBC and SQL*Net services. Step-by-Step setup to query from a SQL Server database.


1. Define a Data Source Name (DSN) for SQL Server

--> The first step is to define a system DSN within the Windows ODBC Data Sources.

a.From the start menu click on Settings -> Control Panel and double click the ODBC icon.

b.Click on the System DSN tab and then click the Add button.

c.Choose the SQL Server driver since this will be a connection to SQL Server. Click Finish to continue with the data source definition.

d.Key in any name you would like to reference this ODBC data source. I have chose SS for simplistic reasons but it should be descriptive to the database you may be connecting to within SQL Server. You may also describe the data source in any way you wish. This is my remote SQL Server that I will be connecting to. Click Next to continue.

e.I selected Sql Server authentication and also provided Login ID and Password below. Then click Next to continue.

f.Typically, this window is populated with the default SQL Server database of "master." Click the check box to change the default database this ODBC connection should connect to and use the drop down list to select. I have chosen to use the my test Remshr database. Click Next to continue.

g.I also left current window alone and clicked Finish.

h.The next window then appears for you to look at the settings you have configured for the data source. Click Test Data Source to validate your definition.

i.TEST COMPLETED SUCCESSFULLY appears, in which case you have successfully configured the data source. Click OK to close all windows as you are done with the data source definition.

j.The end product should be a valid System DSN. You may in the future click on the DSN name and click the Configure button to change the definition if you like. I did this when I wanted to switch between databases. A small warning here on re-configuration of the DSN: you will need to drop and re-create the database link to activate the DSN. Click OK to exit the DSN administrator.


2. Create a Heterogeneous Services Initialization File

Oracle has provided a sample heterogeneous services init file within the %ORACLE_HOME\hs\admin directory. You will need to copy that file to a new file name within the same directory and edit it for the ODBC DSN you have just created. Below you will find the sample heterogeneous services file Oracle provides and then an edited version, which I have given a new name that corresponds to my DSN name.

%ORACLE_HOME\hs\admin\inithsodbc.ora sample file

(ORACLE_HOME=D:\oracle\product\10.1.0\Db_1)

# This is a sample agent init file that contains the HS parameters that are needed for an ODBC Agent.
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = (odbc data_source_name)
HS_FDS_TRACE_LEVEL = (trace_level)
#
# Environment variables required for the non-Oracle system
#
#set (env var)=(value)

%ORACLE_HOME\hs\admin\initSS.ora altered file

# This is a sample agent init file that contains the HS parameters that are needed for an ODBC Agent.
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = SS
HS_FDS_TRACE_LEVEL = OFF

3. Alter your listener.ora file

Here again Oracle has given us a sample listener.ora file to follow for heterogeneous services within the %ORACLE_HOME\hs\admin directory. Below you will find the sample file and the additions I made to my listener.ora file. I made five distinct changes..
Created my own listener name of LISTENERSS
Changed the Port number to 1522
Changed the SID_NAME to my DSN (SS)
Changed the ORACLE_HOME location
Changed the PROGRAM to hsodbc

%ORACLE_HOME\network\admin\listener.ora altered file

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Ntws205.rmsi.com)(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\10.1.0\Db_1)
(PROGRAM = extproc)
)
)

LISTENERSS =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

SID_LIST_LISTENERSS=
(SID_LIST=
(SID_DESC=
(SID_NAME=SS)
(ORACLE_HOME = D:\oracle\product\10.1.0\Db_1)
(PROGRAM=hsodbc)
)
)

4. Alter your tnsnames.ora file

Here again Oracle has given us a sample tnsnames.ora file to follow for heterogeneous services within the %ORACLE_HOME\hs\admin directory. Below you will find the sample file and the additions I made to my tnsnames.ora file.

1. I made four distinct changes.Created a TNS entry named SS

2. Changed the Port number to 1522

3. Changed the SID to my DSN (SS)

4. Added OK to the HS= parameter

%ORACLE_HOME\network\admin\tnsnames.ora altered file

SS =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
(CONNECT_DATA=(SID=SS))
(HS=OK)
)

5. Start the new Listener

This should be self-explanatory but I provide the output here so that you can know what to expect when you start yours.

D:\>lsnrctl start listenerss

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 15-FEB-2008 16:54:55

Copyright (c) 1991, 2004, Oracle. All rights reserved.

Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
System parameter file is d:\oracle\product\10.1.0\db_1\network\admin\listener.ora
Log messages written to d:\oracle\product\10.1.0\db_1\network\log\listenerss.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\PNPKEYipc)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
STATUS of the LISTENER
------------------------
Alias listenerss
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Start Date 15-FEB-2008 16:55:09
Uptime 0 days 0 hr. 0 min. 4 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File d:\oracle\product\10.1.0\db_1\network\admin\listener.ora
Listener Log File d:\oracle\product\10.1.0\db_1\network\log\listenerss.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\PNPKEYipc)))
Services Summary...
Service "SS" has 1 instance(s).
Instance "SS", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

6. Validate the connection to your DSN

You can now validate the connection to your SQL Server database by the normal Oracle tnsping utility.

D:\>tnsping ss

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 15-FEB-2008 17:14:57

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
D:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)) (CONNECT_DATA=(SID=SS)) (HS=OK))
OK (10 msec)


7. Create a Database Link within Your Oracle Database

Since we would like to connect from our Oracle database and select information from the SQL Server database, we need to create a database link just as if we were connecting to any other remote Oracle database. I have used the default sa login from SQL Server but you may wish to create your own.

SQL> create database link ss
connect to sa identified sa by using 'SS';

Database link created.

This is the fun part as it is the culmination of what we were trying to do. You may describe the tables from SQL Server just as you have done with Oracle in the past and then use a SELECT command. Note that my select statement has double quotes and exact upper and lower cases for the SQL Server query. This is required, at least I tried every combination and none worked except this way.

SQL> desc tblemp@ss
Name Null? Type
----------------------------------------- -------- ----------------------------
EmpNo NOT NULL VARCHAR2(4)
EmpInit NOT NULL VARCHAR2(4)
EmpName VARCHAR2(50)
Salutation VARCHAR2(4)
Nationality VARCHAR2(15)
Sex VARCHAR2(1)
DOB DATE
DOW DATE
Qualification VARCHAR2(15)
-------
-------
-------

SQL>select "EmpNo","EmpInit","EmpName" from tblemp@ss;

Source of this article:
http://www.databasejournal.com/features/oracle/article.php/10893_3442661_1