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
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