This guide bellow describe how to setup a database link between a Microsoft SQL Server and the Oracle Database (for Windows NT/2000) using Oracle Heterogeneous Services. Let me if you need to guide from OS AIX.
1) Create the DSN system source in Microsoft ODBC Administrator (e.g. "sqlmis").
2) Adjust the configuration file of the gateway, located in ORACLE_HOME/HS/ADMIN. The name depends on the SID you use for the Heterogeneous Service. We has used the same name sugested by ORACLE guide (hsodbc). In this case 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 = sqlmis |<- ODBC Data Source Name configured in step 1
HS_FDS_TRACE_LEVEL = 0 |<- trace levels are from 0 to 4
HS_OPEN_CURSORS = 300 |<- this parameter has solved the ORACLE error.
3) Configure tnsnames.ora in ORACLE_HOME\NETWORK\ADMIN. Add the following lines to the file:
The tnsnames.ora file contains the service_name parameter. This value is sent to the listener. Once the listener obtains the service_name it looks for a matching registration in its files. When the database starts, the database will automatically register with the listener. The init.ora file contains the service_names parameters of the names to be registered with the listener when the instance starts.