www.databasejournal.com/features/oracle/article.php/3442661
December 3, 2004 3. Alter your listener.ora fileHere 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..
$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 fileHere 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 ListenerThis 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 DSNYou 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 DatabaseSince 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 8. Select some dataThis 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. |
| Go to page: Prev 1 2 |
|
|
|
|
|
|
Jupitermedia Corporation has two divisions: Jupiterimages and JupiterOnlineMedia
Legal Notices, Licensing, Reprints, Permissions, Privacy Policy.
Advertise | Newsletters | Tech Jobs | Shopping | E-mail Offers
