My Oracle Support Banner

Functional Differences Between Database Gateway for ODBC (DG4ODBC) and Specific Database Gateways (DG4MSQL, DG4Sybase, DG4Informix, DG4Teradata) (Doc ID 252364.1)

Last updated on AUGUST 27, 2021

Applies to:

Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Oracle Database Gateway for Sybase - Version 11.1.0.6 to 12.1.0.2 [Release 11.1 to 12.1]
Information in this document applies to any platform.

Details

PURPOSE
-------

To explain the functional differences between the Database Gateway for ODBC (DG4ODBC) and the Database Gateways for specific non-Oracle databases, for example DG4SQL*Server, DG4Sybase, DG4Informix, DG4Teradata.

The Database Gateway for ODBC is a widely used method to use a database link to connect to a non-Oracle database using an ODBC-Driver. It is a generic method, hence it has certain restrictions compared to when using a specific Database Gateway.

.

Actions

Database Gateway for ODBC Restrictions Compared to Specific Database Gateways

The RDBMS feature Heterogeneous Services is an essential part in accessing
non-Oracle systems. There are two types of Heterogeneous Services agents:
* Specific Oracle Database Gateways
* Database Gateway for ODBC

A specific Oracle Database Gateway is a gateway that is designed for accessing a named non-Oracle system, for example SQL*Server, Sybase, Teradata  and Informix.

The Database Gateway for ODBC (DG4ODBC) is basically designed for accessing non-Oracle database for which no specific Database Gateway exists. Therefore it only contains generic code and the customer has to provide the necessary ODBC driver.

To use the Database Gateway for ODBC, you must have an ODBC driver installed on the same machine as the Oracle Gateway.  The ODBC driver manager and driver must meet the following requirements:

On Windows:
- The ODBC driver must have compliance level to ODBC standard 3.0. For
multi-byte support, the driver needs to meet ODBC standard 3.5.
-The ODBC driver and driver manager must conform to ODBC application
program interface (API) conformance Level 1 or higher. If the ODBC driver or
driver manager does not support multiple active ODBC cursors, the
complexity of SQL statements that you can execute using Oracle Database
Gateway for ODBC is restricted.

On UNIX:
- The ODBC driver manager must be installed on the same machine.
- The ODBC driver must have compliance level to ODBC Standard 3.0 and have
a conformance level 1 or higher. If the ODBC driver works with an ODBC
driver manager, the ODBC driver manager must be compliant with ODBC
Standard 3.0 or higher. The ODBC driver must have compliance level to
ODBC standard 3.0. For multi-byte support, the driver needs to meet ODBC
standard 3.5.

The ODBC driver you use must support all of the core SQL ODBC data types and should support SQL grammar level SQL_92.
There are other requirements which are listed in the documentation -

Oracle® Database Gateway for ODBC User’s Guide
12c Release 1

Oracle® Database Gateway for ODBC User’s Guide
11g Release 2 (11.2)

Oracle® Database Gateway for ODBC User’s Guide
11g Release 1 (11.1)


Because DG4ODBC is 'generic' there are some limitations compared to a specific Database Gateway. These are:
- BLOB and CLOB data cannot be read by pass-through queries
- Updates or deletes that include unsupported functions within a WHERE clause are
not allowed
- Does not support stored procedures
- Cannot participate in distributed transactions; they support single-sitetransactions only
- Does not support multithreaded agents
- Does not support updating LONG columns with bind variables
- Does not support rowids
- Any COMMIT or ROLLBACK issued in a PL/SQL cursor loop closes all open cursors,
which can result in the following error:
ORA-1002: fetch out of sequence
To prevent this error, move the COMMIT or ROLLBACK statement outside the cursor
loop.
- there are restrictions on using the following SQL syntax which are detailed in the documentation -
WHERE CURRENT OF Clause
CONNECT BY Clause
ROWID
EXPLAIN PLAN Statement
- Does not support CALLBACK links

Oracle Database Gateway for ODBC assumes that ODBC driver provider that is being
used supports the following minimum set of SQL functions:
- AVG(exp)
- LIKE(exp)
- COUNT(*)
- MAX(exp)
- MIN(exp)
- NOT

Contacts

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.