Oracle Database - Gateway (Heterogeneous services)

Card Puncher Data Processing


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.

More …

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.


  • 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





Discover More
Owb Hs Module Definition Complete
OWB - Creating an ODBC Non-Oracle Module as data source (with Excel)

Oracle Warehouse Builder use the gateway capabilities of the oracle database to create Non-Oracle Module. This article will create an ODBC Non-Oracle Module on Excel data source. To set up the ODBC...
Card Puncher Data Processing
OWB - Source

Location Node : Oracle Database : Any Oracle Database Non-oracle Database : Any database accessible through Oracle : DB2, DRDA, Informix, SQL Server, Sybase, and Teradata. ODBC...
Card Puncher Data Processing
Oracle Database - Gateway Agent Registration

Registration is an operation through which Oracle stores information an gateway agent in the data dictionary. Agents do not have to be registered. If an agent is not registered, Oracle stores information...
Hs Odbc Excel Define Name
Oracle Database - How to read data through an ODBC connection with Gateway (Heterogeneous services)

This article is aimed to show how you can access data from oracle database via an odbc connection direct by using the gateway odbc agent (or program) generic connectivity. In this example, an odbc connection...
Card Puncher Data Processing
Oracle Database - ODBC

Microsoft Open Database Connectivity (ODBC) provides a common C programming interface for applications to access data from database management systems (DBMS). You can access: the (meta-)data of Oracle...
Oracle Database File Location Variable
Oracle Database - System Identifier (SID)

The System Identifier (SID) identifies a specific database instance. The SID uniquely distinguishes the instance from any other instance on the same computer. Each database instance requires a unique SID...

Share this page:
Follow us:
Task Runner