Configuring Generic Database connectivity (Heterogeneous Database HS)

The Example shows how to connect to an Excel sheet. Similar steps can be followed for other data source. Mainly it is the ODBC configuration that could be different

Make sure that you define an ODBC data source in System DSN

The ODBC data source for EXCEL requires that you locate the excel sheet file that you need to access. In other words, the excel file shall seem like your remote database

The following are the needed files

Tnsnames.ora # TNSNAMES.ORA Network Configuration File: C:\ORACLE\ORA92\network\admin\tnsnames.ora

# Generated by Oracle configuration tools.

EXCEL_TEST =

(DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)

(HOST=localhost)

(PORT=1521))

(CONNECT_DATA=

    (SID=testing_hs)

 )

(HS=OK)                              //NOTE:  (HS=OK) IS OUTSIDE THE CONNECT_dATA SECTION

 )

 

 

Listener.ora# LISTENER.ORA Network Configuration File: C:\ORACLE\ORA92\network\admin\listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

)

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = home)(PORT = 1521))

)

)

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = C:\ORACLE\ORA92)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = ORCL)

(ORACLE_HOME = C:\ORACLE\ORA92)

(SID_NAME = ORCL)

)

(SID_DESC =

(SID_NAME = testing_hs)

(ORACLE_HOME = C:\ORACLE\ORA92)

(Program = hsodbc)

)

)

 

***  MAKE SURE THE CHANGE THE ORACLE HOME TO THE CORRECT ORACLE HOME

***  FOR ORACLE 10G, IT WILL BE SOMETHING LIKE  C:\ORACLE\PRODUCT\10.1.2\DB_1

The HS file that is located in the directory

$ORACLE_HOME/hs/admin and file name is inittesting_hs.ora (Same as SID name is listener.ora and tnsnames.ora with init prefix

# 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 = ammar =Ť Data source name in SYSTEM

HS_FDS_TRACE_LEVEL = 0

#

# Environment variables required for the non-Oracle system

#

#set <envvar>=<value>

NEEDLESS TO SAY, YOU HAVE TO BOUNCE THE LISTENER AFTER YOU MAKE THE LISTENER CHANGED

YOU CAN TEST THE TNSNAMES CONFIURATIONS USING 

C>TNSPING  EXCEL_TEST

Testing the configuration

SQL> create database link excel using 'excel_testí;

Database link created.

SQL> select ename from emp$@excel;

 

-- Please note that EMP is the excel sheet name (not the excel file itself, which has already been located by the ODBC connection). Do not forget the $ sign .If you have multi sheets within the file you just change the sheet name Ie  SELECT * from  SHEET3$@excel;

ENAME

----------------------------------------------

AMMAR

ALLEN

WARD

JONES

MARTIN

BLAKE

CLARK

SCOTT

KING

TURNER

ADAMS

ENAME

----------------------------------------------

JAMES

FORD

MILLER

14 rows selected.

 

 

 when you face connection errors due to HS service, check the TRACE directory that is located wihtin the HS directory

 $ORACLE_HOME/hs/trace

 

One error that i encountered during testing was

 

HS Agent diagnosed error on initial communication,
probable cause is an error in network administration
Network error 2: NCR-00002: NCR: Invalid usage
 

The reason for this error was a miss-configuration in the Tnsnames.ora

 

Wrong configuration

EXCEL_TEST =

(DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)

(HOST=localhost)

(PORT=1521))

(CONNECT_DATA=

    (SID=testing_hs)

    (HS=OK)                      -- location of HS=OK should be after the closing bracket of CONNECT_DATA

 )

 

 )

 

correct Configuration

EXCEL_TEST =

(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = AMMAR)(PORT = 1521))
(CONNECT_DATA =
(SID=hs_testing)
)
(hs=ok)
)

 

 

 

Copies of the configuration parameters are listed here for download

inittesting_hs.ora

listener.ora

tnsnames.ora

 

 

IMPORTANT NOTE: Uusing oracle net manager, Net configuration assistant  deletes entries that has (HS=), therefore , after correctly configuring your files, take a copy and try not to use the Net manager, or assistant to further manipulate the net files