Oracle Database - How to read data through an ODBC connection with Gateway (Heterogeneous services)
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.
Articles Related
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
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.
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)
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';
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
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.
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
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.