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 LISTENERMYSQLSERVERDSN
- Changed the Port number to 1522
- Changed the SID_NAME to my DSN (MYSQLSERVERDSN)
- Changed the ORACLE_HOME location
- Changed the PROGRAM to hsodbc
$ORACLE_HOME/hs/admin/listener.ora.sample file
Click for full code
$ORACLE_HOME/network/admin/listener.ora altered file
LISTENERMYSQLSERVERDSN =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
SID_LIST_LISTENERMYSQLSERVERDSN=
(SID_LIST=
(SID_DESC=
(SID_NAME=MYSQLSERVERDSN)
(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 MYSQLSERVERDSN
2. Changed
the Port number to 1522
3. Changed
the SID to my DSN (MYSQLSERVERDSN)
4. Added
OK to the HS= parameter
$ORACLE_HOME/hs/admin/tnsnames.ora.sample file
# This is a sample tnsnames.ora that contains the NET8 parameters that are
# needed to connect to an HS Agent
#hsagent =
# (DESCRIPTION=
# (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
# (CONNECT_DATA=(SID=hsagent))
# (HS=)
# )
$ORACLE_HOME/network/admin/tnsnames.ora altered file
MYSQLSERVERDSN =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
(CONNECT_DATA=(SID=MYSQLSERVERDSN))
(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.
C:\>lsnrctl start listenermysqlserverdsn
LSNRCTL for 32-bit Windows: Version 10.1.0.2.0 - Production on 01-DEC-2004 13:19:06
Copyright (c) 1991, 2004, Oracle. All rights reserved.
Starting tnslsnr: please wait...
TNSLSNR for 32-bit Windows: Version 10.1.0.2.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\listenermysqlserverdsn.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 listenermysqlserverdsn
Version TNSLSNR for 32-bit Windows: Version 10.1.0.2.0 - Production
Start Date 01-DEC-2004 13:19:09
Uptime 0 days 0 hr. 0 min. 2 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\listenermysqlserverdsn.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 "MYSQLSERVERDSN" has 1 instance(s).
Instance "MYSQLSERVERDSN", 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.
C:\>tnsping mysqlserverdsn
TNS Ping Utility for 32-bit Windows: Version 10.1.0.2.0 - Production on 01-DEC-2004 13:19:54
Copyright (c) 1997, 2003, Oracle. All rights reserved.
Used parameter files:
d:\oracle\product\10.1.0\db_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=MYSQLSERVERDSN)) (HS=OK))
OK (30 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 mysqlserverdsn
connect to sa identified by using 'MYSQLSERVERDSN';
Database link created.
8. Select some data
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 employees@mysqlserverdsn
Name Null? Type
----------------------------------------- -------- ----------------------------
EmployeeID NOT NULL NUMBER(10)
LastName NOT NULL VARCHAR2(20)
FirstName NOT NULL VARCHAR2(10)
Title VARCHAR2(30)
TitleOfCourtesy VARCHAR2(25)
BirthDate DATE
HireDate DATE
Address VARCHAR2(60)
City VARCHAR2(15)
Region VARCHAR2(15)
PostalCode VARCHAR2(10)
Country VARCHAR2(15)
HomePhone VARCHAR2(24)
Extension VARCHAR2(4)
Photo LONG RAW
Notes LONG
ReportsTo NUMBER(10)
PhotoPath VARCHAR2(32512 CHAR)
SQL> select "EmployeeID","LastName","FirstName" from employees@mysqlserverdsn;
EmployeeID LastName FirstName
---------- -------------------- ----------
1 Davolio Nancy
2 Fuller Andrew
3 Leverling Janet
4 Peacock Margaret
5 Buchanan Steven
6 Suyama Michael
7 King Robert
8 Callahan Laura
9 Dodsworth Anne
9 rows selected.
I have to say this was fun, although it took me quite
a while to figure some of the specifics and nuances of this type of connection.
I hope that you will not need to spend as much time as I since I have gone
through some of the headaches. I would also suggest you read some of the Oracle
manual "Heterogeneous Connectivity Administrator's Guide" and look at
some of the particulars and issues around interacting with SQL Server through
this ODBC connection. Happy SELECTing.
»
See All Articles by Columnist James Koopmann
» 