Home -->

 

Technical Documents

 - Java Caps (JCAPs)

    - Architecture

    - Domain vs. Logical Host

    - Web Services

    - JCD vs. BPEL

    - Scalability

    - JVM

 - Java

    - JDBC Stored Proc Calls

 - Oracle

    - HS Connectivity Setup

    - Orakill

    - PL/SQL and JSP

    - Queues

Applications

Links

 - Webservices (Axis)

 - PSOUG (Oracle)

 - JCAPs Forum

 

Blog

 - Enter Blog

 

Have a topic I may know about?  Email me

 

 

Heterogeneous Connectivity Setup (Windows)

1) Install the Microsoft Data Access Components (MDAC)SDK ODBC driver. This can
be downloaded from http://www.microsoft.com/data.

2) Install the Oracle Server.

A "Typical Installation" will include "Generic Connectivity using ODBC".  If performing a "Custom Installation", do not omit this component.  Directory "ORACLE_HOME/HS" is created during the installation which includes
sample files.

3) Ensure CATHS has been run.

Oracle Generic Connectivity feature requires the data dictionary objects created by script "ORACLE_HOME\RDBMS\ADMIN\CATHS.SQL".

To test whether CATHS has run, do a "describe" of one of the HS objects. For example, connect to the Oracle server as schema "SYSTEM" and do "DESCRIBE SYS.HS_FDS_CLASS". If it does not exist, connect as SYS (or
internal) and run the CATHS script.

4) Configure an ODBC System Data Source.

From the Control panel select "Data Sources (ODBC)". Click the "System DSN" tab. Click the "Add" button. Select the "SQL Server" driver. In the configuration panel, key a name and description of your choice. Remember the
name, it is required later - on my system I called it "sstest". Select a SQL Server from the drop-down menu. Choose your preference for authentication. I chose "SQL Server authentication" and specified userid "SA" (you can guess the password). Click "Next". I didn't change any further options on the next two panels. Click "Finish", you get a button to "test data source", which worked for me, then clicked "ok" twice to finish.

5) Configure tnsnames.ora. This file is in ORACLE_HOME\NETWORK\ADMIN.
Add an entry, e.g.:

hsodbc.uk.oracle.com =
(DESCRIPTION=
    (ADDRESS=
        (PROTOCOL=tcp)
            (HOST=) |<- adjust hostname for your system
            (PORT=1521) |<- adjust port if not using 1521
        )
       (CONNECT_DATA=
            (SID=hsodbc)
        )
        (HS=)
    )

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

6) Configure listener.ora. This file is also in ORACLE_HOME\NETWORK\ADMIN.
Add the following line to the SID_List of the listener.ora

(SID_DESC=
    (SID_NAME=hsodbc)
    (ORACLE_HOME=d:\oracle\ora81) |<- adjust Oracle_Home? directory
    (PROGRAM=hsodbc)
)

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

7) Restart the Oracle listener:
In an MSDOS window, issue commands "LSNRCTL STOP" then LSNRCTL START". Do "LSNRCTL STATUS" to check there is a service handler for your hsodbc entry.

8) 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 my example the listener SID is "hsodbc", thus 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 = sstest |<- ODBC Data Source Name configured in step 4
HS_FDS_TRACE_LEVEL = 0 |<- trace levels are from 0 to 4 (4 is maximum)
HS_FDS_TRACE_FILE_NAME = sstest.trc |<- adjust as required

The trace file specification can be commented out if not required. Trace files are generated with default names in the ORACLE_HOME/HS/TRACE directory if trace level is not zero.

9) In an SQL*Plus session, create a database link. E.g.:

create database link hsodbc using 'hsodbc';


10) Test the connection. E.g.:

select * from authors@hsodbc;