Making a Connection from Oracle to SQL Server - Page 2

December 3, 2004



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..

  1. Created my own listener name of LISTENERMYSQLSERVERDSN
  2. Changed the Port number to 1522
  3. Changed the SID_NAME to my DSN (MYSQLSERVERDSN)
  4. Changed the ORACLE_HOME location
  5. 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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers