Gateway technology is composed of two parts: a component that has the generic technology to connect to a non-Oracle system, which is common to all the non-Oracle systems, called Heterogeneous Services, and a component that is specific to the non-Oracle system that the gateway connects to. Heterogeneous Services, in conjunction with the Oracle Database Gateways, enable transparent access to non-Oracle systems from an Oracle environment.

Heterogeneous data access is a problem that affects a lot of companies. Many companies run several different database systems. Each of these systems stores data and has a set of applications that run against it. Consolidating this data in one database system is often hard - in large part because many of the applications that run against one database may not have an equivalent that runs against another. Until migration to one consolidated database system is feasible, it is necessary for the various heterogeneous database systems to interoperate.

Oracle Database Gateways provide the ability to transparently access data residing in a non-Oracle system from an Oracle environment. This transparency eliminates the need for application developers to customize their applications to access data from different non-Oracle systems, thus decreasing development efforts and increasing the mobility of the application. Applications can be developed using a consistent Oracle interface for both Oracle and non-Oracle systems.

Heterogeneous Services provides the generic technology for connecting to non-Oracle systems. As an integrated component of the database, Heterogeneous Services can exploit features of the database, such as the powerful SQL parsing and distributed optimization capabilities.

Heterogeneous Services extend the Oracle SQL engine to recognize the SQL and procedural capabilities of the remote non-Oracle system and the mappings required to obtain necessary data dictionary information. Heterogeneous Services provides two types of translations: the ability to translate Oracle SQL into the proper dialect of the non-Oracle system as well as data dictionary translations that displays the metadata of the non-Oracle system in the local format. For situations where no translations are available, native SQL can be issued to the non-Oracle system using the pass-through feature of Heterogeneous Services.

Heterogeneous Services also maintains the transaction coordination between Oracle and the remote non-Oracle system.

Oracle Database Gateway for ODBC is intended for low-end data integration solutions requiring the dynamic query capability to connect from an Oracle database to non-Oracle systems.

Any data source compatible with the ODBC standards described in this chapter can be accessed using Oracle Database Gateway for ODBC.

The capabilities, SQL mappings, data type conversions, and interface to the remote non-Oracle system are contained in the gateway. The gateway interacts with Heterogeneous Services to provide the transparent connectivity between Oracle and non-Oracle systems.

The gateway works with an ODBC driver to access the non-Oracle data store using Oracle Database Gateway for ODBC. The driver that you use must be on the same machine as the gateway. The non-Oracle system can reside on the same machine as the Oracle database or on a different machine.

The gateway can be installed on the machine running the non-Oracle system, the machine running the Oracle database or on a third machine as a standalone. Each configuration has its advantages and disadvantages. The considerations when determining where to install the gateway are network traffic, operating system platform availability, hardware resources and storage.

Topics:

• Oracle and Non-Oracle Systems on Separate Machines
  The figure is an example of a configuration in which an Oracle and non-Oracle database are on separate machines, communicating through Oracle Database Gateway for ODBC. The client connects to the non-Oracle system through a network.
• Oracle and Non-Oracle Systems on the Same Machine
  The figure is an example of a configuration in which an Oracle and non-Oracle database are on the same machine, again communicating through Oracle Database Gateway for ODBC.

To use Oracle Database Gateway for ODBC, you must have an ODBC driver installed on the same machine as the gateway.

The ODBC driver manager and driver must meet the following requirements:

• The following ODBC catalog functions must work inside a transaction:

  • SQLColumns

  • SQLForeignKeys

  • SQLGetFunctions

  • SQLGetInfo

  • SQLGetTypeInfo

  • SQLPrimaryKeys

  • SQLProcedureColumns

  • SQLProcedures

  • SQLStatistics

  • SQLTables

• 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.

  See Also:

  Your ODBC driver documentation for dependencies on an ODBC driver manager, and Oracle Database Concepts for more information on transaction isolation levels.

• The ODBC driver you use must support all of the core SQL ODBC data types and must support SQL grammar level SQL_92. The ODBC driver should also expose the following ODBC APIs:

  • SQLAllocHandle

  • SQLBindCol

  • SQLBindParameter

  • SQLCancel

  • SQLColAttribute

  • SQLColumns

  • SQLConnect

  • SQLDescribeCol

  • SQLDisconnect

  • SQLDriverConnect

  • SQLEndTran

  • SQLExecDirect

  • SQLExecute

  • SQLFetch

  • SQLForeignKeys

  • SQLFreeHandle

  • SQLFreeStmt

  • SQLGetConnectAttr

  • SQLGetData

  • SQLGetDiagField

  • SQLGetDiagRec

  • SQLGetEnvAttr

  • SQLGetFunctions

  • SQLGetInfo

  • SQLGetStmtAttr

  • SQLGetTypeInfo

  • SQLMoreResults

  • SQLNumResultCols

  • SQLParamData

  • SQLPrepare

  • SQLPrimaryKeys

  • SQLProcedureColumns

  • SQLProcedures

  • SQLPutData

  • SQLRowCount

  • SQLSetConnectAttr

  • SQLSetEnvAttr

  • SQLSetDescField

  • SQLSetDescRec

  • SQLSetStmtAttr

  • SQLStatistics - If statistics are to be supported

  • SQLTables