Using Oracle Heterogeneous Services in an
Operational Data Store or Data Warehouse
By John Adolph Palinski
I recently received a user request to bring
information into the data warehouse for use in
combination with other data warehouse tables. On
the surface this is a routine task. After all, I
often bring transaction processing data into the
warehouse using materialized views and other
tools such as the Merge statement. However, the
database for this request was a non-Oracle
database. This created a small problem. I could
no longer use the normal Oracle tools to bring
this data into the warehouse. I would need a
In the past, whenever I needed to bring
non-Oracle data into the warehouse, I required
the production data to be placed into a flat
file and I loaded the file into Oracle using
SQL*Loader. Creating the source system
extraction process, the SQL*Loader script, and
the batch processes that move and execute the
files required quite a bit of work. In the past,
we used this technique for single files.
However, the current request was to move an
entire production system schema into the
warehouse for transformation and loading. This
would require SQL*Loader and batch files for
three hundred and seventy tables. I really did
not want to do this much work so I began to look
for an easier method.
Investigating various sources I identified
Oracle's Hetergeneous Services. This Oracle
feature allows you to set up an ODBC connection
inside the Oracle database. This was the answer
to my problem. With a little configuration,
Oracle can read the non-Oracle database files
directly. No batch programs, flat files, or
SQL*Loader files. Before setting this up I
researched the topic in a variety of sources.
Some of them were incorrect, some were not.
However, none of them discussed setting up
hetergenous services using a remote database.
All of the articles discussed setting using
Oracle on a local Windows server that had an
Oracle installation and the ODBC drivers
installed on the server. However, our data
warehouse was a Unix server and the ODBC drivers
could not be installed on this server. I really
needed instructions that discussed how to
connect from a Unix platform using Oracle to
ODBC drivers located on a Windows server. This
article describes the steps needed to set up
heterogeneous services on a Unix data warehouse
server that reads a Sybase database on another
server. These same steps can be applied to any
non-Oracle database that has ODBC connectivity.
The infrastructure is illustrated in Figure 1.
The following bullets describe the structure:
- The target system is a Sybase database
residing on Windows XP server X.
- Server X has Sybase ODBC drivers installed.
- Server X has an Oracle server or code tree
installed. Server X does not have the Oracle
- The data warehouse resides in an Oracle
database on Unix server Z.
- The users will access the Sybase database
through the Oracle installation on server Z.
The data warehouse residing on the Oracle
installation on server Z needs to communicate
and query the Sybase database on server X.
Below is a series of steps that are needed to
set up the hetergeneous services. The actual
code will be discussed following these steps.
1. Install ODBC drivers on the server (server X)
that is to actually access the Sybase database.
2. Setup the ODBC connection on server X using
the Windows ODBC Data Source Administrator
3. Test the ODBC drivers to ensure that
connectivity is made to the non-Oracle database.
4. Ensure the Global_names parameter is set to
5. Install an Oracle server (or code tree) or an
Oracle database on the same server (server X) as
the ODBC drivers.
6. Configure the Hetergeneous services. This
consists of creating an initodbc.ora file within
the Oracle installation on server X.
7. Modify the Listener.ora file on server X used
to connect to the Oracle instance and ODBC
8. Modify the Tnsnames.ora file on server Z .
This is the Oracle database installation
accessed by the users to connect to the ODBC
9. Reload the listener on server X
10. Create a database link on the Oracle
installation (server Z) that accesses the
11. Run a Select statement for the Oracle
installation (server Z) using the database link.
The first step in setting up hetergeneous
services is to install the ODBC drivers on the
Windows server (server X) that is to directly
access the non-Oracle database. In the case of
Sybase, this is Adaptive Server Anywhere 9.0.
You can identify the current installed drivers
on the ODBC Data Source Administrator. Launch
this dialog box using this path:
Tools/Data Sources. Figure 2 depicts the dialog
box. The System DSN tab page is shown. This tab
page shows the System data sources defined and
the associated drivers. You will need to define
a DSN for the non-Oracle Sybase database.
Steps 2 and 3 - Seting up and testing the ODBC
The first task in setting up the ODBC driver is
to configure the ODBC. This requires you to set
up a System DSN (or data source name). Perform
- Launch the Create New Data Source dialog. On
the System DSN tab page (Figure 2), press the
Add button. This opens the Create New Data
Source dialog box (Figure 3).
- Select the appropriate driver and press the
Finish button. This launches the ODBC
Configuration Dialog box displayed in Figure 3.
The ODBC Configuration dialog box (Figure 4) is
a five tab dialog box and settings will be
needed on the first four tabs. The tabs and
ODBC - Enter a name for the remote data source
(mandatory). This can be a custom name. For our
example the name will be Cas30c. You may also
enter a description for the remote data source
(optional). This can be the actual network sever
name or a TCP/IP alias
Login - Enter a remote data source user id and
password or use the integrated login
Database - Enter the remote server name. This is
the System DSN created on the remote database.
Enter the actual remote database name.
Network - Select the appropriate network
protocols. For example, check TCP/IP and IP=Network_server_name.
Network_server_name is the actual network server
- Return to the ODBC tab. Press the Test
Connection button. If it returns a success
message you are ready to begin setting up the
Heterogeneous services in Oracle.
Step 4 - Ensure the Global_names parameter is
set to false
Execute the following query to determine the
where name like 'global_names%'
If the parameter is set to True, you must modify
the Init.ora file and restart the database.
Step 5 - Install an Oracle server on server X
In this step you must install an Oracle server
on the same server as the ODBC drivers. This
installation can be a database or simply the
code tree. If the users will not access Oracle
directly from this server the code tree is
Step 6 - Setup the Heterogeneous Services file
on server x
You will need to document the parameters that
Oracle needs to locate and execute the ODBC
drivers. Follow these instructions:
- Locate the Oracle_home/hs/admin/initsodbc.ora
- Save a copy of this file renaming it per the
initDSNNAME.ora (i.e. initcas30c.ora)
DSNNAME is the DSN value set up on server X
containing the ODBC (Figure 4).
- Open the init.ora file.
Locate the line "HS_FDS_TRACE_LEVEL = ". Change
Locate the line "HS_FDS_CONNECT_INFO = ". Change
to the data source name.
# 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 = Cas30C
HS_FDS_TRACE_LEVEL = off
# Environment variables required for the
Step 7 - Configuring the Listener
The Listener on server X accessing the Oracle
database must be configured to point to the
Heterogeneous Services entry created in step 6.
Create an entry in the SID _LIST similar to the
one below. The bolded part is the actual entry.
Note the following:
SID_NAME is the DSN for the remote database.
ORACLE_HOME is the actual Oracle home file path.
PROGRAM tells Oracle to use heterogeneous
services. This particular entry tell Oracle to
look for the initcas30c.ora file defined in step
(SID_NAME=Cas30C) -- Enter the DSN on this line
(ORACLE_HOME = c:\oracle10gdb) -- Enter your
Oracle home on this line
(PROGRAM = hsodbc)
Step 8 - Configuring Tnsnames.ora
Oracle needs to know where to look for the
remote database when it is called. This requires
an entry in the Tnsnames.ora file in every
Oracle instance that will be calling the remote
database. In the case of our article, the
Tnsnames.ora file entry must be on server Z. The
Oracle installation on server X does not require
this modification since this Oracle installation
will not be directly used by a user. The server
X Oracle installation is simply the path from
Oracle's listener to the ODBC drivers. The
following example can be followed:
Cas30c.world = -- This name can be customized
-- (Server x)
(PORT=1521))) -- Enter the port on which the
server x Oracle installation
-- is listening
(CONNECT_DATA=(SID=Cas30c)) - Enter the DSN name
(HS=OK) -- Enter this value. It tells Oracle to
Step 9 - Reloading the listener
At this point the infrastructure is in place.
You will need to reload the Listener.ora
settings you defined in Step 7. They must be in
place on server X. There are several methods.
Execute one of the following from the command
After completing this step, you should check
whether the listener and Tnsnames.ora file are
configured correctly. On the server accessing
the data (server Z) issue a Tnsping command from
the command prompt. If Oracle can contact the
server X Oracle installation, the configuration
is correct and a response is seen. The following
exemplifies the command.
Step 10 - Create a database link
The last step in the procedure is to create a
database link on each installation that will
view the non-Oracle data (Server Z). In order to
acccess the non-Oracle database, each Oracle ID
must know how to contact the database. The
database link is placed after the non-Oracle
data source reference. The database link
contains the Tnsnames reference (Cas30c) along
with the remote data user id and password. When
the SQL statement containing the link is
executed, Oracle reviews the Tnsnames.ora file
to locate the server and listener port for the
ODBC database. A message is then sent to that
server. The listener identifies the signal,
locates the hetergeneous services entry, and
uses the ODBC drivers to perform the work. The
results are sent back through this channel.
There are a variety of database link options.
This example is one that contains the remote
database userid and password.
it's important that the username
and password must be in double quotes.
create database link ODBC connect to "sa"
identified by "pencil" using 'hsodbc'.
create [public] database link link_name connect
to "user_name" identified by "password" using 'cas30c';
You have now completed the configuration. You
can test the configuration by executing a Select
statement against the non-Oracle database.
If you need to have data outside the Oracle
database, you will find this a great tool for
making it transparent to the Oracle user.