1 Introduction

This chapter describes the challenges of operating in a heterogeneous environment. Oracle recognizes these challenges and offers both synchronous and asynchronous solutions that enable companies to easily operate in such an environment. The synchronous solution, Oracle Database gateway, is discussed in this guide.

This chapter contains the following topics:

1.1 The Information Integration Challenge

Information integration is a challenge that affects many organizations because they may run several different database systems. Each of these systems store data and has a set of applications that run against the data. This data is just bits and bytes on a file system - and only a database can turn the bits and bytes of data into business information. Integration and consolidation of all business information allows an organization to take advantage of the synergies inherent in business information.

Consolidation of all data into one database system is often difficult. This is primarily because many of the applications that run against one database may not have an equivalent application that runs against another. Until migrating data to one consolidated database system is possible, the heterogeneous database systems must work together.

There are several problems to overcome before interoperability is possible. The database systems can be accessed using different interfaces, different data types, different capabilities, and different ways of handling errors. Even when one relational database tries to access another relational database, the differences are significant. In this situation, the common features of the databases include data access through SQL, two-phase commit protocol, and similar data types.

There are also significant differences. SQL dialects can be different, as can transaction semantics. There can be some data types in one database that do not exist in the other. The most significant area of difference is in the data dictionaries of the two databases. Most data dictionaries contain similar information, but the information is structured for each data dictionary in a different way. There are several ways of overcoming this problem. This guide describes Oracle's approach to synchronously access information from multiple sources.

1.2 How Oracle Addresses Synchronous Information Integration

If a client program must access or modify data on several Oracle databases, it can open connections to each of them. This approach, however, has several drawbacks; among them are the following:

  • To join data from the databases, the client must have logic allowing that.

  • To guarantee data integrit, the client must have transaction coordination logic.

Oracle provides another approach called distributed processing, where the client connects to one Oracle database and shifts the burden of joining data and transaction coordination to that database. The database to which the client program connects is called the local database. Any database other than this one is a remote database. The client program can access objects at any of the remote databases using database links. The Oracle query processor takes care of the joins and its transaction engine takes care of the transaction coordination.

The approach that Oracle took to solve the heterogeneous connectivity problem is to allow a non-Oracle system to be one of the remote nodes in the previously described scenario. The remote non-Oracle system functions as a remote Oracle system. The non-Oracle system uses the same SQL dialect and the same data dictionary structure as an Oracle system. Access to a non-Oracle system is done through Heterogeneous Services.

The work done by the Heterogeneous Services component is, for the most part, completely transparent to the end user. With only a few exceptions (these are noted in later chapters), you are not required to do anything different to access a non-Oracle system than is required for accessing an Oracle system. The Heterogeneous Services component is used as the foundation for implementing Oracle's access to non-Oracle databases.

An Oracle Database gateway works in conjunction with the Heterogeneous Services component of Oracle Database to access a particular, commercially available, non-Oracle system for which that Oracle Database gateway was designed. For example, you use the Oracle Database Gateway for Sybase to access a Sybase database. Oracle also provides an Oracle Database Gateway for ODBC which enables you to use ODBC drivers to access non-Oracle databases.

Using an Oracle Database gateway, you can access data anywhere in a distributed database system without being required to know either the location of the data or how it is stored.


The ODBC drivers that are required by Oracle Database Gateway for ODBC are not supplied by Oracle. Users must obtain drivers from other vendors.

Oracle also offers asynchronous information integration products. Those products are not discussed in this guide. Briefly, these products include:

  • Oracle Streams

    Oracle Streams enables the propagation of data, transactions, and events in a single data stream or queue, either within a database or among multiple databases. Not only can Oracle Streams capture, propagate, and apply changes to data, it can also handle data structure changes (DDL) and user-defined events. Changes can be captured and applied as is, or transformed at any point in the capture, propagation, and application processing.

  • Messaging Gateway

    The Messaging Gateway enables communication between Oracle Database and non-Oracle messaging systems.

  • Open System Interfaces

    Oracle offers a number of open interfaces, such as OCI, JDBC, and ODBC, that enable customers to use third-party applications or to write their own client applications to access Oracle Database.

1.3 Benefits of Oracle's Solution for Synchronous Information Integration

Much of the processing power of Oracle Database gateways is integrated into the database. This provides an efficient solution for information integration that enables full exploitation of the power and features of the Oracle database. This includes such features as powerful SQL parsing and distributed optimization capabilities.

The following sections describe the benefits of Oracle's approach to resolving the challenges of a heterogeneous environment:

1.3.1 Remote Data Can Be Accessed Transparently

Oracle Database gateways provide the ability to transparently access data in non-Oracle databases from an Oracle environment. You can create synonyms for the objects in a non-Oracle database and refer to them without having to specify a physical location. 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.

Instead of requiring applications to interoperate with non-Oracle systems using their native interfaces (which can result in intensive application-side processing), applications can be built upon a consistent Oracle interface for both Oracle and non-Oracle systems.

1.3.2 No Unnecessary Data Duplication

Oracle Database gateways provide applications direct access to data in non-Oracle databases. This eliminates the need to upload and download large amounts of data to different locations, thus reducing data duplication and saving disk storage space. By eliminating uploading and downloading large amounts of data, there is a reduced risk for unsynchronized or inconsistent data.

1.3.3 SQL Statements Can Query Several Different Databases

An Oracle database accepts SQL statements that query data stored in several different databases. An Oracle database with the Heterogeneous Services component processes the SQL statement and passes the appropriate SQL code directly to other Oracle databases and through gateways to non-Oracle databases. The Oracle database combines the results and returns them to the client.

1.3.4 Oracle's Application Development and End User Tools Can Be Used

Oracle Database gateways extend the range of Oracle's database and application development tools. Oracle has tools that increase application development and user productivity by reducing prototype, development, and maintenance time.

You are not required to develop new tools or learn how to use other tools to access data stored in non-Oracle databases. Instead, you can access Oracle and non-Oracle data with a single set of Oracle tools. These tools can run on remote systems connected through Oracle Net to an Oracle database.

1.3.5 Users Can Communicate With a Remote Database in its Own Language

Oracle enables you to transparently access non-Oracle systems using SQL statements. In some cases, however, it becomes necessary to use non-Oracle system SQL to access the non-Oracle system. For such cases, Heterogeneous Services has a passthrough feature that enables you to bypass Oracle's query processor and to communicate with the remote database in its own language.