How to setup generic connectivity (HSODBC) for 32 bit Windows (Windows NT, Windows 2000, Windows XP, Windows 2003)

 
출처 :  https://metalink.oracle.com/



PURPOSE
------------
This article provides a step-by-step guide for setting up and troubleshooting
generic connectivity using ODBC, also called Generic Connectivity, for a Windows based platform.

Please be aware that HSODBC is NOT ported to the Windows 64 bit platforms yet.


SCOPE & APPLICATION
--------------------------
This bulletin describes a setup of Generic Connectivity running on
Windows to access a MS Access 97 MDB file using the ODBC driver:
Microsoft Access Driver.


What Is Generic Connectivity?
-----------------------------
Generic connectivity is intended for low-end data integration solutions requiring the
ad hoc query capability to connect from Oracle  to non-Oracle database systems.
Generic connectivity is enabled by Oracle Heterogeneous Services, allowing you to connect to
non-Oracle systems with improved performance and throughput.

Generic connectivity is implemented as a Heterogeneous Services ODBC agent. An ODBC agent
is included as part of your Oracle system. Be sure to use the agent shipped with your particular
Oracle system and installed in the same $ORACLE_HOME.


Generic Connectivity Architecture
---------------------------------
To access the non-Oracle data store using generic connectivity, the agent works
with an ODBC driver. Oracle provides support for the ODBC driver interface.
The driver that you use must be on the same machine as the agent. The non-Oracle data
stores can reside on the same machine as the Oracle installation or on a different machine.


How to setup a database link between a Microsoft MDB file and
the Oracle Database using Generic Connectivity for ODBC
-----------------------------------------------------------------

1) With the OUI (Oracle Universal Installer) install Generic Connectivity using ODBC.
This product is part of the server installation, because a listener is needed. 
A directory called HS is created.
  
2) The Generic Connectivity utility needs data dictionary tables in the Oracle database.
To check their existence, run a query on  i.e. SYS.HS_FDS_CLASS.
 If it fails, run the caths.sql script located in ORACLE_HOME\RDBMS\ADMIN\  as user sys or internal.

3) Install the third party ODBC driver from Microsoft.

4) With ODBC Admin Utility configure a SYSTEM DATASOURCE.  
i.e. specify as Data Source Name mstest and select as mdb file  the Northwind.mdb from Microsoft.
 
5) Now configure tnsnames.ora. This file is in ORACLE_HOME\NETWORK\ADMIN. 
Add the following lines to the file:

   hsodbc.de.oracle.com  =
      (DESCRIPTION=
        (ADDRESS=(PROTOCOL=tcp)
        (HOST=<HOSTNAME>)           |<- adjust hostname
        (PORT=1521))                         |<- adjust port
        (CONNECT_DATA=(SID=hsodbc))
        (HS=OK)
      )    

  Please make sure the HOST and PORT entries refer to the ORACLE listener
  which contains the HSODBC SID entry. It MUST NOT refer to the foreign database
  host and port.
  Note, a sample file is located in ORACLE_HOME\HS\ADMIN.

6) Configuring listener.ora:
   This file is in ORACLE_HOME\NETWORK\ADMIN.
   Add the following line to the SID_List of the listener.ora and restart the
   listener afterwards. ( After the restart a service handler for hsodbc should

   exist).
        (SID_DESC=
           (SID_NAME=hsodbc)
           (ORACLE_HOME=e:\oracle)  |<- adjust Oracle_Home directory
           (PROGRAM=hsodbc)
         )

   Note, a sample file is located in ORACLE_HOME\HS\ADMIN.

7) Adjust the configuration file of the gateway.  It is located in
   ORACLE_HOME\HS\ADMIN. The name depends on the SID you use for the Heterogeneous Service.
   In our sample the listener SID is hsodbc and so the configuration file is inithsodbc.ora.

   # 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 = mstest      |<- ODBC Data Source Name configured in step 4
   HS_FDS_TRACE_LEVEL = off               |<- trace levels are from off to debug

8) Testing the connectivity between Oracle database and the mdb file:
   - create a datbase link:
     create database link hsodbc using 'hsodbc';
   - select * from article@hsodbc;

   In case of case sensitive usernames and passwords of the foreign database
   (for example Sybase, SQL Server, Informix...), the username and password
   must be surrounded by double quotes like:
       create database link hsodbc connect to "Hsodbc" identified by "hsodbc" using 'hsodbc';