OraFusion.com - Oracle Heterogeneous Services

Oracle Articles

Modifications made on 15 May 2005:

1. Added section on Transparent Gateways for MSSQL.
2. Revised and updated section on Generic Heterogeneous Services

Generic Heterogeneous Services and Transparent Gateways in 10 Minutes:

This article is a quickstart guide to setting up connectivity between Oracle (9i and above) and non-Oracle datasources using Heterogeneous Services. The first section deals with Generic Heterogeneous Services, which use ODBC to connect to the source database. The second section covers Transparent Gateways, which use native drivers. SQL Server is the the source database in both sections.

1. Generic Heterogeneous Services

Oracle Generic Heterogeneous Services (HS) provide a mechanism for connecting to a non-Oracle database via ODBC. Once set up, the non-Oracle datasource can be queried as any other (remote) Oracle database using database links. Step-by-step instructions for setting up Generic HS are detailed in this section .

A caveat before proceeding further: in general only tables and views can be accessed via HS.

The non-Oracle datasource used in the discussion is the "Northwind" database that is supplied with default SQL Server (SQL2K) installs. Similar steps should apply to any ODBC accessible database. In my tests, I have got Generic HS to work with Access and Hyperion Pillar.

The steps involved in setting up Generic HS are detailed below:

Set up an ODBC datasource called "NORTH" (or any old name that you choose) for the Northwind database.
Set the HS_FDS_CONNECT_INFO parameter in the inithsodbc.ora configuration file. The file is located in <ORACLE_HOME>\hs\admin, where <ORACLE_HOME> is your Oracle installation directory (C:\oracle\ora92 in this example). The parameter should be set to the name of the ODBC datasource created in the previous step - i.e. "NORTH" in the case at hand. The config file should be saved as init<SID Name>.ora, where <SID Name> is the name that you want to refer to the database by. This does not have to be the same as the ODBC datasource name. Let's assume we want to call the SID NRTH. We'll then need to save the file as initNRTH.ora

Here's a listing of initNRTH.ora:

HS_FDS_CONNECT_INFO=NORTH
#HS_DB_NAME=NORTH
#HS_DB_DOMAIN=<your oracle database domain>
#HS_FDS_TRACE_LEVEL = ON
#HS_FDS_TRACE_FILE_NAME = <log file name>

HS_FDS_CONNECT_INFO is a mandatory parameter. HS_DB_DOMAIN is required only if you have non-default entries for NAMES.DEFAULT_DOMAIN in sqlnet.ora.
Modify listener.ora file (on the Oracle server) to include the non Oracle datasource. The default location for this file is <ORACLE_HOME >\network\admin on Windows and <ORACLE_HOME >/network/admin on Unix platforms. Here's the listener.ora file for our example:

LISTENER=
  (DESCRIPTION_LIST=
    (DESCRIPTION=
      (ADDRESS LIST=
        (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>)(PORT = 1521))
      )
      (ADDRESS LIST=
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
      (ADDRESS LIST=
        (ADDRESS = (PROTOCOL = TCP)(HOST =<hostname>)(PORT = 1524))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME = C:\oracle\ora92)
      (SID_NAME = ORCL)
     )
    (SID_DESC =
      (PROGRAM = extproc)
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oracle\ora92)
   )
    (SID_DESC =
      (PROGRAM = hsodbc)
     (SID_NAME = NRTH)
       (ORACLE_HOME = C:\oracle\ora92)
     )
   )

Notes:
a) The relevant entries are the third ones in each of the lists.
b) The SID_NAME for the non-Oracle datasource should match the configuration file name set in the previous section.
c) The oracle supplied executable hsodbc.exe does the work of connecting to the ODBC datasource.
d) You will need to restart the listener after making these entries.
Enter network info for non-Oracle datasource in tnsnames.ora on the client. The default location for this file is <ORACLE_HOME >\network\admin on Windows and <ORACLE_HOME >/network/admin on Unix platforms. The SID should match the SID_NAME in listener.ora.

---start tnsnames excerpt---------

NORTH=
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST =<your Oracle host machine>)(PORT = 1524))
     )
    (CONNECT_DATA =
       (SID = NRTH)
     )
     (HS=OK)
   )

-----------end tnsnames excerpt--------------

Notes:
a) HS=OK specifies that this is a heterogeneous datasource.
b) Make sure that the port number matches the listener port set in the previous section.
Create database link pointing to the heterogeneous datasource:

create database link nrth connect to <username> identified by <password> using 'NRTH';

Note:
a) For non-password protected databases (such as MS Access) the SQL would be:

create database link nrth using 'NRTH';
Test connection from SQL Plus or your favourite Oracle tool:

Select count(*) from suppliers@nrth;

This query should return a count of 29 records if your Northwind database has not been modified.

That's it. You should now be able to access all tables in the Northwind database from your Oracle tool, using SQL or PL/SQL. There's just one word of caution: you may not be able to access TEXT and NTEXT columns depending on the ODBC driver you use. I tested both the Microsoft supplied ODBC driver and the PB Merant ODBC driver; both had problems with these datatypes.

2. Transparent Gateway for SQL Server

The relevant SQL Server transparent gateway setup and documentation files are located in <ORACLE_HOME>/tg4msql, where ORACLE_HOME is your Oracle installation directory. We'll set up the transparent gateway to connect to the Northwind database on a server named YourServer. Setting up the gateway is very straightforward. No ODBC datasource setup is required - Oracle will use its own optimised driver. Here are the steps to setting up the gateway. If you have read the previous section on Generic HS, you will notice that steps 2 through 5 below mirror steps 3 through 6 in the previous section:

Set the HS_FDS_CONNECT_INFO parameter in the inittg4msql.ora file (the file is located in <ORACLE_HOME>/tg4msql/admin). The parameter should be set to server_name.database_name, where server_name is the SQL Server database server name and database_name is the name of the database you want to connect to. The file should be saved as init< Gateway SID>.ora where <Gateway SID> is the name you want to refer to the SQL Server by. This can be any name you choose -we'll call our Gateway SID "MSQL". Here's a listing of initMSQL.ora:

HS_FDS_CONNECT_INFO=YourServer.Northwind
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

The remaining parameters have not been modified from their default values.
Modify listener.ora file on the Oracle server to include the Gateway SID. Here's the listener.ora file for our example:

LISTENER=
  (DESCRIPTION_LIST=
    (DESCRIPTION=
      (ADDRESS LIST=
        (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>)(PORT = 1521))
      )
      (ADDRESS LIST=
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
      (ADDRESS LIST=
        (ADDRESS = (PROTOCOL = TCP)(HOST =<hostname>)(PORT = 1524))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME = C:\oracle\ora92)
      (SID_NAME = ORCL)
     )
    (SID_DESC =
      (PROGRAM = extproc)
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oracle\ora92)
   )
    (SID_DESC =
      (PROGRAM = tg4msql)
     (SID_NAME = MSQL)
       (ORACLE_HOME = C:\oracle\ora92)
     )
   )

Remember to restart the listener after adding the entry.
Add an entry for the gateway to the tnsnames file on the client. Below is an excerpt from tnsnames.ora for the gateway SID for our example:

---start tnsnames excerpt---------

MSQL=
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST =<your Oracle host machine>)(PORT = 1524))
     )
    (CONNECT_DATA =
       (SID = MSQL)
     )
     (HS=OK)
   )

-----------end tnsnames excerpt--------------

Make sure that the port number entered matches the corresponding entry in the listener file.
Create database link pointing to the heterogeneous datasource:

create database link msql connect to <username> identified by <password> using 'MSQL';
Test connection from SQL Plus or your favourite Oracle tool:

Select count(*) from suppliers@msql;

This query should return a count of 29 records if your Northwind database has not been modified.

In my tests I have noticed at least a couple of advantages of Transparent Gateways over Generic HS. These are:

Improved performance.
Better handling of SQL Server TEXT datatype columns.

Unlike Generic HS, Transparent Gateways must be licensed separately from Oracle Corporation. Depending on your data transfer requirements, this may or may not be worth the extra cost.

Back to the top