Access Firebird, InterBase, ISAM, RMS and more from Oracle; on Windows by using DG4ODBC.

Accessing ODBC data sources with Oracle Database Gateway for ODBC (DG4ODBC) on Windows

Contents

Introduction

The Oracle Database Gateway for ODBC (DG4ODBC) lets you transparently integrate ODBC data sources with Oracle.

DG4ODBC for Microsoft Windows lets Oracle client applications access non-Oracle data in databases for which a Windows ODBC driver is available. The data remains in its native store, but to the Oracle application it 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 Salesforce, InterBase, Firebird, and MongoDB.

DG4ODBC interacts with Heterogeneous Services (an integrated Oracle database component) to provide transparent connectivity between Oracle and non-Oracle systems. DG4ODBC uses a Windows ODBC driver to access the non-Oracle system.

Starting with Oracle 11g, DG4ODBC is included in the Oracle distribution.

DG4ODBC supports Oracle 11g and later. For Oracle 10g, a patch is required).

The article describes how to:

  1. Install and configure the ODBC driver on the Oracle server.
  2. Configure Oracle:
    • Heterogeneous Services (initSID.ora)
    • Database listener (listener.ora)
    • Network client (tnsnames.ora)
  3. Create a database link with SQL*Plus.

Throughout this tutorial, replace ORACLE_HOME with your Oracle home directory path. For example, c:\apps\Administrator\product\11.1.0\db_1\.

Access Firebird, InterBase, ISAM, RMS and more from Oracle; on Windows by using DG4ODBC.

Install, configure, and test the Windows ODBC driver

On Windows, Oracle uses the Microsoft ODBC Driver Manager to access the ODBC driver. All Easysoft Windows ODBC drivers are compatible with the Microsoft ODBC Driver Manager. Essentially, we are linking DG4ODBC 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 is installed.
  2. In the Windows ODBC Data Source 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, you can configure Oracle 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.
  2. Install and license the Firebird ODBC driver on the machine where DG4ODBC is installed.

    For installation instructions, refer to the Firebird ODBC driver User's Guide.

  3. In the Windows ODBC Data Source 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, choose Test.

Configure Oracle

To use DG4ODBC, you need to edit three files:

initSID.ora references the system ODBC data source that you created in ODBC Data Source 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 a connection failure.

Create an 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 located in ORACLE_HOME\hs\admin folder.

Each DG4ODBC instance needs a separate initSID.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 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 Data Source Administrator. Note that as DG4ODBC 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're 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 ORACLE_HOME\hs\trace. 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 don't want to use multiple active statements or the ODBC driver doesn't support this feature, un-comment the HS_FDS_SUPPORT_STATISTICS setting by deleting the # character from the start of the line.
Access Firebird, InterBase, ISAM, RMS and more from Oracle; on Windows by using DG4ODBC.

3.2 Edit listener.ora

The Oracle listener listens for incoming requests from the Oracle database. For the Oracle listener to listen for DG4ODBC requests, 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.

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. You include this entry when creating the database link with SQL*Plus.

Reference the SID_NAME you created for DG4ODBC 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.

Restart the listener

Restart the Oracle 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.ora alias and restarting the listener, use tnsping alias to check that you can connect to the new service. If tnsping succeeds, you'll get a message similar to:

tnsping fb
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, you need to create a database link in SQL*Plus. At the SQL prompt, enter:

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. Note that when creating the database link, the database user and password need to be supplied.

In our example, "dbuser" and "dbpassword" are a valid Firebird user name and password.

'fb_connection' is the tnsnames.ora entry you created to identify DG4ODBC 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 myfbtable@hslink;

If you have any problems configuring DG4ODBC with an Easysoft driver, contact the Easysoft Support team ().

Known issues

When trying to access SQL_WCHAR data, data is either incorrectly returned or an Oracle error is returned. This is because Easysoft 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. (Refer to Oracle Bug 6993468 - FAILURE TO QUERY USING DG4ODBC - VARCHAR DATA OUTPUT IS GARBLED.)

Access Firebird, InterBase, ISAM, RMS and more from Oracle; on Windows by using DG4ODBC.