Oracle Database Discussions

Hi All,

I've used HSODBC on Windows platform with MS Access. I prepared a document for my reference may be it is bit helpful to you also.

Any suggestion and comment is highly appreciated.


Oracle's Heterogeneous Services (HS)


Heterogeneous Services (HS) is an integrated component within the Oracle database server and provides the generic technology for accessing non-Oracle systems from the Oracle database server. Heterogeneous Services enables you to use Oracle SQL statements to transparently access data stored in non-Oracle systems as if the data resided within an Oracle database server. To access a non-Oracle system, you need to use a Heterogeneous Services agent. If you connect to the non-Oracle system using generic connectivity through the ODBC or OLE DB interfaces, the agent is an executable that it automatically installed with the Oracle database server.
Although HS service can be configured to work with variety databases like MS Access, MS Excel, SQL Server etc. This article shows how Oracle's Heterogeneous Services can be configured to connect to a Microsoft Access database using standard databases link. The method described can be used to connect to MS-Access from about any platform - Unix/Linux or Windows.

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>\network\admin\caths.sql

as user sys or internal.

In this article the platform used is windows.
ORACLE_HOME = <ORACLE_HOME>
MS Access file is C:\MDB_HSODBC.mdb




Necessary Components:
· Oracle Database (with HS component installed).–Check for the <ORACLE_HOME>HS directory if it is not there you need to install HS service in oracle.
· Oracle Network and Database Listener.
· ODBC driver for your data source (EXCEL, ACCESS, etc)


Following components need to be configured:
· ODBC driver for the data source.
· Database Listener (listener.ora).
· Network Client (tnsnames.ora).
· Heterogeneous Services (init_odbc_data_source.ora) (sqlnet.ora).
· Database Link.


For this example I will be querying an MS Access (MDB_HSODBC.mdb) from SQL*Plus. Following is the step by step procedure to configure these components.


1. ODBC Data Source configuration:
Start -> Settings
Control Panel
Administrative Tools
Data Sources (ODBC)
Select System DSN Tab


Click on Add button

Select appropriate driver in my case it is MS Access to work with so I selected the highlighted driver.



1. Enter appropriate Data Source Name (DSN_ACCESS in my example).
2. Enter description if you want it to be (optional).
3. Click on select button and Browse you mdb file.



2. Database Listener configuration:
Here is a sample LISTENER.ORA entry that can be used. Change the HOST, PORT and ORACLE_HOME entries to match your setup. You may also use a different SID_NAME if required.
<ORACLE_HOME>\network\admin\listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = access_25)(PORT = 1524))
)
)

Port à Your port number must match with the port number of TNS in next step.
access_25 à Is the name of machine where oracle is installed. You can use IP address.


SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(PROGRAM = hsodbc) ß hsodbc program needs to be called.
(SID_NAME = SID_HSODBC) ß Your SID Name. (must be a valid name)
(ORACLE_HOME = <ORACLE_HOME>)
)
)


You can add your entries in existing DESCRIPTION_LIST & SID_LIST after adding you entries you file would look something like this.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC3))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = access_25)(PORT = 1524))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = <ORACLE_HOME>)
(PROGRAM = extproc)
)
(SID_DESC =
(PROGRAM = hsodbc)
(SID_NAME = SID_HSODBC)
(ORACLE_HOME = <ORACLE_HOME>)
)
)


Note:- when listener configuration is over you need to restart your listener in order to accept changes you have made.



3. Network Client (TNS) configuration:

Let us move on the next step i.e. configuration of tnsnames.ora file.

<ORACLE_HOME>\network\admin\tnsnames.ora

TNS_HSODBC=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =access_25)(PORT = 1524))
)
(CONNECT_DATA =
(SERVICE_NAME = SID_HSODBC)
)
(HS=OK)
)


1. You can use any valid name for you TNS in my case it is TNS_HSODBC
2. Your port number must match with the port number of listener of previous step.
3. Your SID is the SID_NAME that you have entered in listener’s file.
4. You must add (HS=OK) in this entry it tells oracle to use Heterogeneous Service.


4. Heterogeneous Services configuration:

<ORACLE_HOME>\hs\admin\init******.ora

****** à Is SID_NAME you have given in step 2.

If your SID_NAME is ABC the file should be initabc.ora this name must match with your SID_NAME. This file doesn’t get created automatically but you need to create this. There is a file named inithsodbc.ora copy and paste this file and simply change the name accordingly. If your SID_NAME is hsodbc then no need to create a file you can modify existing file.

In my example it is initSID_HSODBC.ora

HS_FDS_CONNECT_INFO = DSN_ACCESS à DSN Name of Step 1.

HS_FDS_TRACE_LEVEL = off

HS_NLS_DATE_LANGUAGE ='DD-MM-YYYY'
HS_NLS_DATE_FORMAT ='DD-MM-YYYY'



One more file needs to modified i.e. sqlnet.ora here are the steps.

<ORACLE_HOME>\network\admin\sqlnet.ora

If there are parameters like

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

You must comment it out or modify to

SQLNET.AUTHENTICATION_SERVICES= (NONE)
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

We have completed HS configuration let us move on to the next step to use it and get it worked.

5. Database Link creation:

CREATE PUBLIC DATABASE LINK LNK_HSODBC
CONNECT TO DB_ACCESS
IDENTIFIED BY DB_PASSWORD
USING ‘TNS_HSODBC’ ;


DB_ACCESS à User Name “Can be anything doesn’t matter”.
DB_PASSWORD à If you have password in MS Access database then it must match that password otherwise it can be anything but you need to mention it.
SELECT
*
FROM
STU_DTL@LNK_HSODBC ;
STU_DTL à Table Name in MS Access database.


If you want to make your query more simpler you can create synonym for your database link.

CREATE PUBLIC SYNONYM SYNO_HSODBC à Synonym Name.
FOR STU_DTL@LNK_HSODBC ;


Now you can query data source using synonym. This is just a shortcut.

SELECT
*
FROM
SYNO_HSODBC ;









Thanks & Regards
Manoj

Message was edited by:
Manoj91