About

Gateway (Or Heterogeneous Services before 11g) make a non-Oracle system appear as a remote Oracle Database server through program that are called agent.

The Oracle ODBC agent acts as an ODBC client and then the ODBC/OLEDB datasource must be accessible on the machine where the Oracle HSODBC agent is running.

The agent can be :

• an Oracle Transparent Gateway

A transparent gateway agent is a system-specific source and behaves as if you had an Oracle database. You can then use the Oracle language (function, …) but you must purchase an agent (a special program).

• or the ODBC generic connectivity agent included with Oracle Database. (known from 11g as Oracle Database Gateway for ODBC Agent). Generic connectivity is intended for low-end data integration solutions and the transfer of data is subject to the rules of specific odbc or OLE DB drivers installed on the client machine. In this case, you do not need to purchase a separate transparent gateway, you can use the generic connectivity agent included with the Oracle Database server.

Articles Related

More …

• Oracle Database - Gateway Data Dictionary
• Oracle Database - Gateway - Data Dictionary (from the remote data source) - The Translation Views
• Oracle Database - Gateway Architecture
• Oracle Database - How to read data through an ODBC connection with Gateway (Heterogeneous services)
• Oracle Database - Gateway Log

Possible data store

Gatway enables integration with data stores such as :

• DB2,
• SQL Server
• and Excel,
• transaction managers like CICS
• and message queuing systems like WebSphere MQ.

When an heterogeneous services source is created, an SID is also created to refer to it.

Benefits

• Remote Data Can Be Accessed Transparently
• No Unnecessary Data Duplication
• SQL Statements Can Query Several Different Databases
• Oracle's Application Development and End User Tools Can Be Used
• Users Can Communicate With a Remote Database in its Own Language

Difference between Oracle Transparent Gateway and the generic connectivity agent.

The generic connectivity agent (DG4ODBC) don't support stored procedure and distributed transactions and must be installed on the same machine as the database while the agent gateway talk with the same language than the database and support natively the datatype conversion.

Check here for a complete list of features and restrictions by agent : Data sheet Oracle Generic Connectivity and Oracle Transparent Gateway®

Documentation / Reference

Documentation:

• Oracle® Database Heterogeneous Connectivity User's Guide 11g Release 2 (11.2)
• Oracle® Database Heterogeneous Connectivity Administrator's Guide 10g Release 2 (10.2)

Example:

• How Do I Load Data Stored in a Microsoft Excel File?
• Example

Home:

• Oracle Gateway Home

Download:

• The Download file is in the same section than the database