Oracle Database - How to read data through an ODBC connection with Gateway (Heterogeneous services)

Create the Excel Sheet and define a Name

To source data from an Excel file, you must define a name for the range of data being sourced:

• In the employee worksheet, highlight the range that you want to query from Oracle. The range should include the column names and the data. Ensure that the column names confirm to the rules for naming columns in the Oracle database.
• Define an Name :
  • For Excel 2003 : From the Insert menu, select Name and then define
  • For Excel 2007 : From the Formula menu, select Define a Name

Create an ODBC System Dsn Connexion

ODBC - Open Database Connectivity

Create the Heterogeneous Services Initialization File

To configure the agent, you must set the initialization parameters in the Oracle Database - Gateway (Heterogeneous services) initialization file. Each agent has its own heterogeneous services initialization file.

The name of the Heterogeneous Services initialization file is initSID.ora, where SID is the Oracle system identifier used for the agent. This file is located in the ORACLE_HOME/hs/admin directory.

You can find the example file 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 = <odbc data_source_name>
HS_FDS_TRACE_LEVEL = <trace_level>


#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>

Copy it and create the initExcel_Employee_SID.ora file in the ORACLE_HOME/hs/admin directory as follows:

#
# Important parameters
#
HS_FDS_CONNECT_INFO = DSN_Excel_Employe
HS_AUTOREGISTER = TRUE

#
# Possible parameters
#
HS_DB_NAME = Employe
HS_FDS_TRACE_LEVEL = 0

Here :

• DSN_Excel_Employe is the name of the system DSN you created in the Create an odbc_system_dsn_connexion Step.
• Excel_Employee_SID is the name of the Oracle system identifier used for the agent.
• The HS_DB_NAME and HS_DB_DOMAIN initialization parameters define the global name of the non-Oracle system.

Set up the listener on the agent

Set up the Oracle Database - Listener on the agent to listen for incoming requests from the Oracle Database server. When a request is received, the agent spawns a Heterogeneous Services agent.

To set up the Oracle Database - Listener, modify the entries in the Oracle Database - Configuration of the Listener (listener.ora) file located in the $ORACLE_HOME/network/admin directory as follows:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = Excel_Employee_SID)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(PROGRAM = hsodbc)
)
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
)

• The SID_NAME parameter must contain the name of the configuration file you created in the previous step. However, it must not contain the init prefix. For example, if the configuration file you created in the previous step was initdb2.ora, then the value of the SID_NAME parameter should be db2.
• Ensure that the ORACLE_HOME parameter value is the path to your Oracle home directory.
• The value associated with the PROGRAM keyword defines the name of the executable agent and will differ for each type of data source.

Remember to restart the Oracle Database - Listener after making these modifications.

If you fire the command lsnrctl status, you must see the new Excel_SID service.

C:\Documents and Settings\Administrator>lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.2.0.4.0 - Production on 20-OCT-2009 05:01:27

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORACLE10G)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
......................
......................
Services Summary...
Service "Excel_Employee_SID" has 1 instance(s).
  Instance "Excel_Employee_SID", status UNKNOWN, has 1 handler(s) for this service...
......................
.....................
The command completed successfully

Create a Net Service Name

To be able to create the database link, we need to add a net service name in the tnsnames.ora file located in the $Oracle_Home>\Network\Admin\ directory.

Verify (of add) the following lines to the tnsnames.ora file where SID contains the same name as was used in SID_NAME in the listener.ora file:

Excel_Employe_Net_Service_Name =
         (DESCRIPTION =
           (ADDRESS_LIST =
             (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
           )
           (CONNECT_DATA =
             (SID = Excel_Employe_SID)
           )
           (HS = OK)
         )

Verify with tnsping in a command dos that you can reach it :

C:\Documents and Settings\Administrator>tnsping Excel_Employe_Net_Service_Name

TNS Ping Utility for 32-bit Windows: Version 10.2.0.3.0 - Production on 20-OCT-2009 07:01:53

Copyright (c) 1997, 2006, Oracle.  All rights reserved.

Used parameter files:
E:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = ORACLE10G)(PORT = 1521))) (CONNECT_DATA = (SID = Excel_Employe_SID)) 
(HS = OK))
OK (30 msec)

Create the database link

Creation of the Oracle Database - Database Link :

• for excel, no credentials are needed

create database link excel_employe_dblink USING 'Excel_Employe_Net_Service_Name';

• for SQl Server:

connect / as sysdba
create public database link SqlServerLink connect to "Username" identified by "Password" using 'DNS Name';

Excel

If you’re querying from an excel file :

• You need to place table or column names between quotes
• the worksheets are seen as the tables and you need to add a $ to the end of the names.

SQL> select "Name" from "Sheet1$"@excel_employe_dblink;
Name
--------------
Nicolas
Gerard
gerardnico

• the name define in the first step are seen as tables

SQL> select "Id" from "Employee"@excel_employe_dblink;

        Id
----------
         1
         2
         3

ORA-02068: following severe error from EXCEL_DBLINK

SQL> Select * From "Sheet1$"@excel_dblink;
Select * From "Sheet1$"@excel_dblink
                        *
ERROR at line 1:
ORA-02068: following severe error from EXCEL_DBLINK
ORA-03135: connection lost contact

You forgot to add in the tnsnames.ora the option HS=OK.