Accessing ODBC Databases from Windows with Oracle Database Gateway for ODBC (DG4ODBC)
The Oracle Database Gateway for ODBC (DG4ODBC) allows you to build heterogeneous solutions that transparently integrate ODBC databases with Oracle.
DG4ODBC for Microsoft Windows enables Oracle client applications to access non-Oracle data in databases for which an MS Windows ODBC driver is available. The data remains in its native store, but to the application appears to be stored in a single, local Oracle database.
This tutorial shows how to use an Easysoft ODBC driver to connect Oracle DG4ODBC on Windows (2000, XP, 2003, Vista, 7, 8) to heterogeneous data such as InterBase and Firebird databases, RMS data, and local or remote C-ISAM/D-ISAM files.
DG4ODBC for Windows interacts with Heterogeneous Services (an integrated Oracle database component) to provide transparent connectivity between Oracle and non-Oracle systems. DG4ODBC uses an Windows ODBC driver to access the non-Oracle system.
DG4ODBC for Windows is in the Oracle 11g and Oracle 12c distributions. DG4ODBC for Windows is also available from the Oracle Technology (OTN) Software Downloads Page. (Starting from this page, choose the Oracle database version you want. Then in the subsequent page, use the "See All" link to display the gateways zip file for Windows.)
DG4ODBC for Windows supports Oracle 12c, Oracle 11g and Oracle 10g (patch required).
This document describes how to use an ODBC driver to access a non-Oracle system from Oracle through DG4ODBC for Windows. The document assumes that the Oracle database server is configured and the DG4ODBC for Windows components are already installed on a Windows machine.
To connect Oracle to a non-Oracle system through DG4ODBC for Windows:
- Install and configure the ODBC driver on the machine where DG4ODBC for Windows is installed.
- Configure Oracle:
- Create a database link.
Throughout this tutorial, replace ORACLE_HOME with the path of your Oracle home directory. For example, c:\apps\Administrator\product\11.1.0\db_1\.
On Windows, Oracle uses the Microsoft ODBC Driver Manager to access the ODBC driver. All Easysoft’s Windows ODBC drivers are compatible with the Microsoft ODBC Driver Manager. Essentially, we are linking DG4ODBC for Windows to the ODBC Driver Manager, and the Driver Manager is then responsible for ODBC access. The relevant components are:
SQL*Plus -> Oracle Client -> DG4ODBC instance -> Microsoft ODBC Driver Manager -> ODBC driver -> Database
You need to:
- Install the ODBC driver on the machine where DG4ODBC for Windows is installed.
- In the Windows ODBC Administrator, add a System ODBC data source that connects to the target database.
- Test the data source.
After you have done this to ensure that the ODBC connection is working, Oracle can be configured to use the connection.
To access Firebird from Oracle, we used our Firebird ODBC driver to set up the prerequisite ODBC connection. These instructions show you how to install the driver and create and test a System ODBC data source. Although the instructions are based on the Firebird ODBC driver, the process is the same for any Windows ODBC driver from Easysoft.
- Download the Firebird ODBC driver for Windows platforms. (Registration required.)
- Install and license the Firebird ODBC driver on the machine where DG4ODBC is installed.
For installation instructions, see the Firebird ODBC driver User’s Guide.
- In the Windows ODBC Administrator, create a System ODBC data source that connects to the Firebird database you want to access from Oracle.
- Test your System ODBC data source. In the driver’s ODBC DSN Setup dialog box, click Test.
3.0 Oracle Configuration
To configure DG4ODBC for Windows, you need to edit three files and restart the Oracle Listener. The files are:
- The gateway init file
The gateway init file references the System ODBC data source that you added in the ODBC Administrator. Entries that you create in tnsnames.ora are subsequently referenced in listener.ora. Care needs to be taken when configuring these files, as an incorrect entry in any of them can lead to connection failure.
On the Oracle server, log in using an account that belongs to the Administrators group. Make a copy of the template gateway init file, initdg4odbc.ora. The template file is stored in the ORACLE_HOME\hs\admin folder.
Every instance using DG4ODBC for Windows needs a separate init*.ora file. For this tutorial, we copied the template init file, initdg4odbc.ora, to initfb.ora, a naming convention that identified our target database. The init file references the System ODBC data source for the target database. For example:
# This is a sample agent init file that contains the HS parameters that are # needed for the Database Gateway for ODBC # # HS init parameters # HS_FDS_CONNECT_INFO = my_system_odbc_dsn #HS_FDS_TRACE_LEVEL = DEBUG #HS_FDS_SUPPORT_STATISTICS=FALSE # # Environment variables required for the non-Oracle system # #set <envvar>=<value>
|HS_FDS_CONNECT_INFO||The name of the system ODBC data source that you added in the Windows ODBC Administrator. Note that as DG4ODBC for Windows uses the SQLDriverConnect ODBC API, you can actually put any valid ODBC connection string here.|
|HS_FDS_TRACE_LEVEL||Specifies the level of tracing. You should generally leave this commented out, as tracing can degrade performance. However, if you are having problems, HS_FDS_TRACE_LEVEL may be set:
HS_FDS_TRACE_LEVEL = Debug
This setting generates a log file for each SQL*Plus connection. Gateway log files are stored in the ORACLE_HOME\hs\trace folder. For example, ORACLE_HOME\hs\trace\FB_agt_1516.trc.
|HS_FDS_SUPPORT_STATISTICS||If you want to use multiple active statements and the ODBC driver supports this feature (which, for example, the Firebird ODBC driver does), leave HS_FDS_SUPPORT_STATISTICS commented out:
#HS_FDS_SUPPORT_STATISTICS = FALSE
If you do not want to use multiple active statements or the ODBC driver does not support this feature, un-comment the HS_FDS_SUPPORT_STATISTICS setting by deleting the # character from the start of the line.
The Oracle Listener listens for incoming requests from the Oracle database. For the Oracle Listener to listen for DG4ODBC, information about DG4ODBC must be added to the Oracle Listener configuration file, ORACLE_HOME\network\admin\listener.ora. You need to:
- Create a SID_NAME for DG4ODBC for Windows.
- Specify the executable that the listener should start in response to DG4ODBC for Windows connection requests.
SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (SID_NAME=fb) (ORACLE_HOME=c:\app\Administrator\product\11.1.0\db_1) (PROGRAM=dg4odbc) ) )
|SID_NAME||The name of the gateway init file without the init prefix or ora extension. For example, we named the init file initfb, and so used fb as the SID_NAME.|
|ORACLE_HOME||The location of your Oracle home directory.|
This is the executable name of the Windows Oracle Database Gateway for ODBC.
Save the file when you have entered this information.
The final Oracle file to edit is ORACLE_HOME\network\admin\tnsnames.ora. You need to add a connect descriptor for the gateway. The connect descriptor identifies the Oracle server to attach to and the SID_NAME to use. For example:
fb_connection= (DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=oracle_host)(PORT=1521)) (CONNECT_DATA= (SID=fb)) (HS=OK) )
|fb_connection||The connect descriptor for DG4ODBC for Windows. You include this entry when creating the database link with SQL*Plus. |
Reference the SID_NAME you created for DG40DBC in the connect descriptor. For example, "fb_connection". Do this so that your tnsnames.ora and listener.ora entries can be attributed to each other.
|ADDRESS||This is the entry for your Oracle server. For example:
|CONNECT_DATA||This must specify the SID_NAME entry you created in listener.ora. For example:
|HS=OK||Specifies that this connect descriptor connects to a non-Oracle system.|
Save the file when you have entered this information.
3.4 Start/Restart the listener
You need to restart the listener so that it uses the new listener.ora entries. To do this, open an MS-DOS window and run:
cd ORACLE_HOME\bin lsnrctl stop lsnrctl start
After adding the tnsnames alias and restarting the listener, use tnsping <alias> to check that you can connect to the new service. If tnsping succeeds, you will get a message similar to:
tnsping fb TNS Ping Utility for 32-bit Windows: Version 22.214.171.124.0 - Production on 06-MAY-2008 17:19:20 Copyright (c) 1997, 2007, Oracle. All rights reserved. Used parameter files: c:\app\Administrator\product\11.1.0\db_1\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=fedora11)(PORT=1521)) (CONNECT_DATA= (SID=fb_connection)) (HS=OK)) OK (0 msec)
If tnsping successfully connects, you can now create a database instance and try to connect to it.
To access an ODBC data source through DG4ODBC for Windows, you need to create a database link. To do this, connect to SQL*Plus as if connecting to your regular database. At the SQL prompt, create a new database link. For example:
CREATE PUBLIC DATABASE LINK hslink CONNECT TO "dbuser" IDENTIFIED BY "dbpassword" using 'fb_connection';
The previous command creates an Oracle database link named hslink that references the tnsnames.ora entry you created for DG4ODBC for Windows. Note that when creating the database link, the database user and password need to be supplied.
"dbuser" and "dbpassword" must be a valid Firebird user name and password.
'fb_connection' is the tnsnames.ora entry you created to identify DG4ODBC for Windows and must be enclosed in single quotes.
After creating the database link, try verifying the connection to the ODBC data source by running a SQL query. For example:
select * from dual@hslink;
where "dual" is a table in your linked database.
If you have any problems using DG4ODBC for Windows with Easysoft ODBC drivers, please contact the Easysoft support team ().
5.0 Known Issues
When trying to access SQL_WCHAR data, data is either incorrectly returned or an Oracle error is returned. This is because Easysoft’s Windows ODBC drivers use little endian (UCS-2LE) format for SQL_WCHAR data, but DG4ODBC currently expects big endian (UCS-2BE) SQL_WCHAR data. This issue is currently being investigated by Oracle and should be resolved in Oracle version 126.96.36.199. (See Oracle Bug 6993468 - FAILURE TO QUERY USING DG4ODBC - VARCHAR DATA OUTPUT IS GARBLED on the OracleMetaLink site.)
- Easysoft ODBC drivers for Windows platforms:
- Salesforce ODBC driver
- MongoDB ODBC driver
- Firebird ODBC driver
- InterBase ODBC driver
- Oracle ODBC driver
- C/D-ISAM ODBC driver
- LINC Developer ODBC driver
- RMS ODBC driver
- System Z ODBC driver
- ODBC-ODBC Bridge — extending DG4ODBC access to any remote ODBC data source. For example, use the ODBC-ODBC Bridge with our ISAM ODBC driver to access remote C-ISAM and D-ISAM files from Oracle on Windows.
- ODBC-JDBC Gateway — extending DG4ODBC access to JDBC data sources.
- Accessing SQL Server from Oracle with Database Gateway for ODBC (DG4ODBC) — use DG4ODBC to connect Oracle 10g, 11g, 12c on UNIX and Linux to SQL Server (or any other database for which an ODBC driver is available).
- Accessing ODBC and JDBC Data Sources from Oracle Heterogeneous Services (HSODBC) — how to connect Oracle to non-Oracle systems with HSODBC, DG4ODBC’s predecessor.
- 64-bit ODBC — everything you need to know about ODBC on 64-bit Linux, UNIX and Windows platforms.
- Oracle Database Gateway documentation: