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

Card Puncher Data Processing

About

This article is aimed to show how you can access data from oracle database via an odbc connection direct by using the gateway odbc agent (or program) generic connectivity.

In this example, an odbc connection is made for excel but it's also of course valuable for other Database management system (DBMS).

The principle is that Oracle see the Odbc data source as an instance and then you can access it via a database link.

Steps

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

Hs Odbc Excel Define Name

Create an ODBC System Dsn Connexion

ODBC - Open Database Connectivity

Windows Odbc Dsn Excel

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.

The generic odbc connectivity agent name is :

  • In versions prior to 11g: hsodbc.
  • In Oracle Database 11g: dg4odbc.

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

The instruction (HS = OK) is really important.

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)

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

Username and password must be in double quotes to avoid that Oracle (in 10G) transform them in upper case because SQL Server is sensitive to the case.

Ensure that the initialization parameter GLOBAL_NAME is set to FALSE to verify that the global naming is not enforced for the database link.

Query it

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

Discover the ODBC database with the data dictionary

Through the ODBC Api, Oracle creates the data dictionary of the remote ODBC source. You can find the details in this article: Oracle Database - Gateway - Data Dictionary (from the remote data source) - The Translation Views

Example:

select * from ALL_CATALOG@excel_employe_dblink
OWNER TABLE_NAME TABLE_TYPE
Sheet1$ TABLE
Sheet2$ TABLE
Sheet3$ TABLE
Employee TABLE

Documentation / Reference

Support

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.

Logging

HSODBC

To enable HSODBC logging, in init

.ora, set HS_FDS_TRACE_LEVEL to 4, and specify a log file with HS_FDS_TRACE_FILE_NAME. For example:
HS_FDS_TRACE_LEVEL = 4
HS_FDS_TRACE_FILE_NAME = /tmp/hsodbcsql.trc

DG4ODBC

To enable DG4ODBC logging, in init

.ora, set HS_FDS_TRACE_LEVEL to Debug. For example:
HS_FDS_TRACE_LEVEL = Debug

The log output will be written to a file in the ORACLE_HOME/hs/log directory.

After you have made these changes to init

.ora, restart the Oracle Listener.





Discover More
Owb Hs Module Definition Complete
OWB - Creating an ODBC Non-Oracle Module as data source (with Excel)

Oracle Warehouse Builder use the gateway capabilities of the oracle database to create Non-Oracle Module. This article will create an ODBC Non-Oracle Module on Excel data source. To set up the ODBC...
Card Puncher Data Processing
Oracle Database - Gateway (Heterogeneous services)

Gateway (Or Heterogeneous Services before 11g) make a non-Oracle system appear as a remote Oracle Database server through program that are called agent. The Oracle ODBC agent acts as an ODBC client and...



Share this page:
Follow us:
Task Runner