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 to heterogeneous data such as InterBase and Firebird databases, RMS data, and local or remote C-ISAM/D-ISAM files.

1.0 Introduction

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:

  1. Install and configure the ODBC driver on the machine where DG4ODBC for Windows is installed.
  2. Configure Oracle:
    1. Configure the gateway init file (init*.ora).
    2. Configure Oracle® Net for the gateway (listener.ora).
    3. Configure the Oracle® database for gateway access (tnsnames.ora).
  3. 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\.

2.0 Install, Configure and Test the Windows ODBC Driver

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:

  1. Install the ODBC driver on the machine where DG4ODBC for Windows is installed.
  2. In the Windows ODBC Administrator, add a System ODBC data source that connects to the target database.
  3. 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.

  1. Download the Firebird ODBC driver for Windows platforms. (Registration required.)
  2. 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.

  3. In the Windows ODBC Administrator, create a System ODBC data source that connects to the Firebird database you want to access from Oracle®.
  4. 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 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.

3.1 Create a Gateway Init File

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>

Where:

Parameter Description
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.

3.2 Edit listener.ora

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:

For example:

SID_LIST_LISTENER=
     (SID_LIST=
           (SID_DESC=
                 (SID_NAME=fb)
                 (ORACLE_HOME=c:\app\Administrator\product\11.1.0\db_1)
                 (PROGRAM=dg4odbc)
           )
     )

Where:

Variable Value
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.
PROGRAM
dg4odbc

This is the executable name of the Windows Oracle® Database Gateway for ODBC.

Save the file when you have entered this information.

3.3 Edit tnsnames.ora

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

Where:

Variable Value
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:
ADDRESS=(PROTOCOL=TCP)(HOST=oracle_host)(PORT=1521)
CONNECT_DATA This must specify the SID_NAME entry you created in listener.ora. For example:
CONNECT_DATA=(SID=fb)
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 11.1.0.6.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.

4.0 Create a Database Link

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 11.1.0.7. (See Oracle® Bug 6993468 - FAILURE TO QUERY USING DG4ODBC - VARCHAR DATA OUTPUT IS GARBLED on the Oracle®MetaLink site.)

Appendix A: Resources

Article Feedback

* Did this content help you?
* Please select one option based on your first choice:

(* Required Fields)


Oracle is a registered trademark of Oracle Corporation and/or its affiliates.