How to Configure Oracle‑MySQL Transparent ODBC Gateway and Fix Missing WHERE Clause Issues

1. Oracle ODBC Gateway Overview

The Oracle database gateway enables transparent access to heterogeneous databases from an Oracle environment, reducing custom application code and supporting data migration.

2. Architecture

The gateway consists of Heterogeneous Services and the Oracle ODBC gateway, which together allow Oracle to communicate with non‑Oracle systems via ODBC.

Client connects to Oracle via Oracle Net.

Heterogeneous Services (HS) connects to the Oracle ODBC gateway.

The gateway talks to the ODBC driver manager and the selected ODBC driver.

For each DBLINK session a dedicated agent process is created and terminated when the session ends.

3. Environment and Versions

Server 1 (RHEL 7.3): Oracle 11.2.0.4

Server 2 (RHEL 7.7): MySQL 8.0

4. Configuration Steps (performed on the Oracle server)

Verify that the Oracle transparent gateway is installed Oracle 11.2.0.4 includes the dg4odbc gateway by default.

Install a Driver Manager Use unixODBC to manage ODBC drivers.

# yum install unixODBC*
# rpm -qa | grep unixODBC
unixODBC-devel-2.3.1-11.el7.x86_64
unixODBC-2.3.1-11.el7.x86_64

Install the MySQL ODBC driver Download the RPM from the official MySQL site and install it.

# rpm -ivh mysql-connector-odbc-8.0.19-1.el7.x86_64.rpm

Configure odbc.ini The file defines DSNs used by the gateway.

[mysql_iom86]
Description    = ODBC for MySQL
Driver         = /usr/lib64/libmyodbc8w.so
Server         = xx.xx.xx.86
Port           = 3306
User           = username
Password       = password
Database       = test
CHARSET        = utf8

Test the ODBC connection with isql Run isql mysql_iom86 to verify connectivity.

Configure the HS gateway file

# more initdg4mysql86.ora
##HS Configuration
HS_FDS_CONNECT_INFO = mysql_iom86
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_LANGUAGE=AMERICAN_AMERICA.zhs16gbk
HS_NLS_NCHAR = UCS2
HS_FDS_FETCH_ROWS=1000
HS_RPC_FETCH_REBLOCKING=OFF
##ODBC Configuration
set ODBCINI=/etc/odbc.ini

Update listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC=
      (SID_NAME=dg4mysql86)
      (ORACLE_HOME=/oracle/app/product/11.2.0/dbhome_1)
      (PROGRAM=dg4odbc)
    )
  )

Configure tnsnames.ora

Mysql86 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = dg4mysql86)
    )
    (HS = OK)
  )

Create a DBLINK and test data access

SQL> create DATABASE LINK link_mysql86 connect to "username" identified by "password" using 'mysql86';

Large‑table queries (tens of millions of rows) were executed successfully, confirming the gateway works.

5. Problem: WHERE Clause Not Passed to MySQL

When querying a large MySQL table through the gateway, the WHERE condition on a VARCHAR column was dropped, causing a full‑table scan and eventual ORA‑03113 communication error.

Trace files showed that the UNICODE driver ( libmyodbc8w.so) mapped the column to Oracle NHCHAR/NVARCHAR2, causing the WHERE clause to be removed. The ANSI driver ( libmyodbc8a.so) kept the condition.

6. Solution

Switch the ODBC driver in odbc.ini from the UNICODE driver to the ANSI driver: Driver = /usr/lib64/libmyodbc8a.so After the change, the WHERE clause is correctly transmitted to MySQL, and query performance returns to expected levels.

7. Summary

The Oracle transparent gateway provides a powerful way to access heterogeneous databases. This article detailed the configuration steps for an Oracle‑MySQL ODBC gateway, highlighted a subtle bug where the WHERE clause was lost, and demonstrated that selecting the ANSI ODBC driver resolves the issue.