|
Category :
Client
Applications
DB Version
:
Oracle 8i / 9i
OS Details :
Solaris 9, Solaris 10
It
is not uncommon these days for companies to use multiple database
products and often requirements come up where data exchange occurs
between oracle and Sqlserver or MS-ACCESS. When such a
requirement was given to me, I utilized oracle's heterogeneous
services that works in conjunction with the ODBC software to make this
work. To access the sqlserver database object, a call will be make
within oracle to a database link which causes oracle to lookup the
tnsnames.ora file which directs it to the listener that runs hsodbc
executable which in turn interacts with the sqlserver odbc driver that
then uses the unix odbc software to talk to sqlserver.
Call Trace:
Database link => tnsnames.ora => listener => init<HA identifier>.ora
=> odbc.ini => odbc driver => odbc software => sqlserver
We
need the following components to make it work:
(1)
ODBC software for unix /
linux
(2)
SQLSERVER / MS-ACCESS ODBC
drivers for UNIX
(3)
Oracle’s
Heterogeneous Agent
(4)
Database Link
Step 1 :
Installation of ODBC software for unix
/ linux
There
are many providers of the ODBC Software for unix but I downloaded the
software from
http://www.unixodbc.org/
. Here are the steps for the software installation:
- Make
sure the location of make executable is included in the PATH. (e.g)
PATH=$PATH: /usr/ccs/bin
- Install
the software by running this from the unix prompt (example):
$ ./configure
--prefix=/ora9i/Software/Odbc/Installation --sysconfdir=/ora9i/Software/Odbc/Installation/etc
--enable-gui=n
Prefix => Installation location
Sysconfdir => Location of ini files
- Run
“make” command followed by “make install” command to install required
libraries
Step 2 :
Installation
of SQLSERVER / MS-ACCESS ODBC drivers for unix
Again,
there are many providers of this software and I got mine from openlink
(http://www.openlinksw.com).
They provide a 2 week evaluation license that you can use to configure
and test. Steps:
- Copy
the software to an install location and run install.sh
- The
installation will
update odbc.ini and odbcinst.ini files
- Edit
odbc.ini under /ora9i/Software/Odbc/Installation/bin to include the
following details:
[sql_lite]
Username = <sqlserver login name>
Password = <Sqlserver login password>
Database = <Sqlserver database name>
- Edit openlink.ini under
/ora9i/Software/Odbc/Installation/bin to include the following
details:
[Environment SQLServer 2000]
TDSHOST = <sqlserver
machine> ; Point this to your SQLServer machine
TDSPORT =
<Port to which connection will be made> ; Default is 1433
[Environment SQLServer
2005]
TDSHOST = <sqlserver
machine> ; Point this to your SQLServer machine
TDSPORT =
<Port to which connection will be made>
Step 3:
Setup Oracle’s
Heterogeneous Agent
-
- Under $ORACLE_HOME/hs/admin,
create a file called init<hs identifier>.ora.
Eample: inithr_test.ora.
- Contents
of inithr_test.ora file:
HS_FDS_CONNECT_INFO =
sql_lite <= Same as name within the odbc.ini file
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME =
/ora9i/Software/Odbc/Installation/lib/libodbc.so
#
# ODBC specific
environment variables
#
set ODBCINI=/ora9i/Software/Odbc/Installation/bin/odbc.ini
- Add a new listener or
add entries for HA agent for an existing listener within the
listener.ora file as follows:
SID_LIST_LISTENER_HS =
(SID_LIST=
(SID_DESC=
(SID_NAME=hr_test)
<= Should match init file for HA agent
(ORACLE_HOME=/ora9i/app/product/9.2)
(PROGRAM=hsodbc)
<= Executable can be found under $ORACLE_HOME/bin
(ENVS =LD_LIBRARY_PATH=/usr/lib:/ora9i/app/product/9.2/lib:/ora9i/Software/Odbc/Installation/lib)
)
)
LISTENER_HS =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=<unix machine name>)(PORT=1522))
)
)
- Make
entries within the tnsnames.ora file for the database link to resolve
as follows:
(Make sure that the SID
matches with the listener’s SID value)
HSSQLSERVER.WORLD,HSSQLSERVER =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL= TCP)(Host=<unix machine name>)(Port= 1522))
(CONNECT_DATA = (SID = hr_test)) (HS=OK))
Step 4 :
Create database link to access the remote data
Create database link
test_link connect to <username> identified by <password> using ‘hssqlserver’;
<username>/<password>
is a valid username / password combination within the sqlserver
database.
You are now all set to
access sqlserver from within the oracle database!!
|
