Using an Oracle database link to connect to Excel

Recently I had to extract data from 5 Access databases and 4 Excel files and merge the data into an Oracle table. One method was to export the data from Access to Oracle via ODBC (at that stage I had not heard of Workbench ) but I was sure that there must be a way of doing the same but then from Oracle’s side. After searching the web I was able to find bits and pieces and eventually all the pieces fitted together to allow me to connect to Excel and Access by using Oracle Heterogeneous Service, which has been available since Oracle 8i. If one searches the web using “Heterogeneous Service” you will find many links but when I started my search I had never heard of this service. The following Oracle documentation has alot of information on this subject, alot more than I though there was to know, for instance that there are Heterogeneous Service (HS) data dictionary views.
Here are the 5 basic steps required to create a database link to non-Oracle sources:

1. The first step is to create a System DSN in the Windows ODBC Data Source.
Start-up the ODBC Data Source Administrator tool (Control Panel > Administrative Tools), select the System DSN tab and click the add button. I will be using an Excel file in this example but you could do the same for an Access database, SQL Server or even MySQL. Give the Data Source a name e.g DEMO_EXCEL and select the excel file.

2. Next the Hetrogeneous Srvice needs to be configured. Go to the directory <OracleHome>\hs\admin\ and make a copy of the file inithsodbc.ora, rename it e.g. inithsdemo_excel. Ensure that the file name always starts with “iniths”. Open the renamed file and alter the: HS_FDS_CONNECT_INFO to contain the name of the just created ODBC Data Source. For the Excel file I also added “HS_AUTOREGISTER = TRUE” and “HS_DB_NAME = hsodbc” to the file.

HS_FDS_CONNECT_INFO = ” DEMO_EXCEL”
HS_FDS_TRACE_LEVEL = off
HS_AUTOREGISTER = TRUE
HS_DB_NAME = hsodbc

3. Next the Oracle listener needs to be configured. Go to the directory <OracleHome>\Network\Admin\ and open the file listener.ora. Add the following lines to the file (in the same area as the other SID_DESC). The SID_NAME has the name of the file created in the <OracleHome>\hs\admin\ directory (excluding init at the start and .ora at the end).

(SID_DESC =
(SID_NAME = hsdemo_excel)
(ORACLE_HOME = C:\oracle\product\10.1.0\Dbase)
(PROGRAM = hsodbc)
)

For the listener changes to take effect we need to restart the listener ( in dos – lsnrctl stop (start)).

4. All that still needs to be done to be able to create the database link is to add some lines to the tnsnames.ora file in the <OracleHome>\Network\Admin\ directory. Add the following lines to the file where SID contains the same name as was used in SID_NAME in the listener.ora file:

MY_EXCEL.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SID = hsdemo_excel)
)
(HS = OK)
)

5. Now the Oracle database link can be created and tested.

Create database link my_excel_file using ‘MY_EXCEL.WORLD’

Select *
From “countries$”@ my_excel_file;
–where countries is the name of a worksheet in my excel file.

Some tips:
If you’re querying from an excel file, the worksheets are seen as the tables and you need to add a $ to the end of the names. If you have lower case names in your Access database or Excel file (table or column names)  you need to use lower case in the query and place it between quotes. Finally, if the data in Access or Excel is greater than 255 characters, Oracle will translate it to a long data type and this has caused my sessions to be killed whenever I did a select * type query. Ensure that the Excel file you’re querying is closed, or you will get errors.

That’s how easy it is. Have fun using Oracles HS (you can even use Oracle’s analytic functions in your queries). If you would like to connect to a MySQL database you apparently need to use the MySQL ODBC 3.51 Driver available from MySQL for Windows.