Oracle8 Distributed Database Systems Release 8.0 A58247-01 |
Library | Product | Contents | Index

# 6
Understanding Oracle Heterogeneous Services

This chapter describes the basic concepts of the Oracle Heterogeneous Services. Topics include:

- What is Heterogeneous Services?
- The Services provided by Heterogeneous Services
- Using Heterogeneous Services

## What is Heterogeneous Services?

Heterogeneous Services is an integrated component within the Oracle8 server, and provides the generic technology for accessing non-Oracle systems from the Oracle server. Heterogeneous Services enables you to:

- Use Oracle SQL to transparently access data stored in non-Oracle systems as if the data resides within an Oracle server.
- Use Oracle procedure calls to transparently access non-Oracle systems, services, or application programming interfaces (APIs), from your Oracle distributed environment.

To access a particular non-Oracle system, you will need a complementary Heterogeneous Services agent.

Note: The phrase "non-Oracle system" denotes both non-Oracle datastores (or databases) that are accessed using SQL, and systems that are accessed procedurally.

### Heterogeneous Services Agents

While Heterogeneous Services provides the generic technology in the Oracle8 server, a Heterogeneous Services agent is required to access a particular non-Oracle system. Oracle Corporation will provide Heterogeneous Services agents in the form of Oracle Open Gateways version 8 and higher.

Oracle Open Gateways is one family of products that will use the Heterogeneous Services. Other products that are based on Heterogeneous Services are being developed. These products, developed by Oracle or third-parties, may not be part of the Oracle Open Gateways family of products. We use the phrase "Heterogeneous Services agents" to denote all products that are based on Heterogeneous Services, including Oracle Open Gateways.

## The Services provided by Heterogeneous Services

Heterogeneous Services provides three services:

- Transaction service
- SQL service
- Procedural service

### Transaction Service

The transaction service allows non-Oracle systems to be integrated into Oracle transactions and sessions. Users transparently set up an authenticated (i.e. username and password) session in the non-Oracle system when it is accessed for the first time over a database link within an Oracle user session. At the end of the Oracle user session, the session is transparently closed at the non-Oracle system. Additionally, one or more non-Oracle systems can participate in an Oracle distributed transaction. When an application commits a transaction, Oracle's two-phase commit protocol will access the non-Oracle system to transparently coordinate the distributed transaction. In fact, the Oracle server will support distributed transaction with the non-Oracle system, even if the non-Oracle system itself does not support two-phase commit.

Both the SQL service and procedural service use the Transaction service. Oracle's object transaction service will use agents that only implement the transaction service.

See "Views for the Transaction Service" on page 7-9 for more information on heterogeneous distributed transactions.

### SQL Service

The SQL service is used to transparently access the non-Oracle system using SQL. If an application's SQL request requires data from a non-Oracle system, Heterogeneous Services translates the Oracle SQL request into an equivalent SQL request understood by the non-Oracle system, accesses the non-Oracle data, and makes the data available to the Oracle server for (post) processing.

The SQL service provides capabilities to:

- transform Oracle's SQL into a SQL dialect understood by the non-Oracle system
- transform SQL requests on Oracle's data dictionary tables to requests on the non-Oracle system's data dictionary
- map non-Oracle system datatypes onto Oracle's datatypes

### Procedural Service

Heterogeneous Services enable users to access any procedural non-Oracle system, such as messaging and queuing systems, from an Oracle8 server. The non-Oracle system is called from the Oracle server using a PL/SQL remote procedure call (RPC). Heterogeneous Services translates the PL/SQL call into a procedure or function of the non-Oracle system.

With the procedural service you can create distributed external procedures, that enable you to call third generation language (3GL) routines from PL/SQL. Like PL/SQL external procedures, the distributed external procedure maps PL/SQL procedure and function names and arguments onto 3GL routine names and their arguments. Both external procedures and distributed external procedures use the same mechanisms to call 3GL routines from PL/SQL. External procedures are designed to perform special purpose tasks that are local to the Oracle8 server, whereas distributed external procedures are designed to access non-Oracle systems. The primary differences between distributed external procedures and external procedures are:

- distributed external procedures enable the Oracle8 server to start authenticated sessions at the non-Oracle system and to coordinate a distributed transaction with the non-Oracle system
- distributed external procedures are considered to run on remote systems, and are therefore invoked through database link

PL/SQL external procedures, are covered in the PL/SQL User's Guide and Reference.

## Using Heterogeneous Services

Heterogeneous Services makes a non-Oracle system appear to be a remote Oracle server. To access or manipulate tables or to execute procedures in the non-Oracle system, you simply create a database link. Tables and procedures at the non-Oracle system can be accessed by qualifying the tables and procedures with the database link. This is identical to accessing tables and procedures at a remote Oracle server.

If a non-Oracle system is referenced, Heterogeneous Services will translate the SQL statement or PL/SQL remote procedure call into the appropriate statement at the non-Oracle system.

Consider the following example that accesses a non-Oracle system through a database link:

    SELECT *
    FROM EMP@non_Oracle_system;

Heterogeneous Services will translate the Oracle SQL statement into the SQL dialect and execute the SQL statement at the non-Oracle system.

### Heterogeneous Services Process Architecture

An agent is required to access a particular non-Oracle system from an Oracle8 server. The Oracle server communicates with the agent. The agent communicates with a particular non-Oracle system.

As shown in Figure 6-1, agents can reside on the same machine as the non-Oracle system but are not required to. The agent can also reside on the same machine as the Oracle8 server, or it can even reside on a third machine. The agent must be accessible by the Oracle8 server through Net8, and the agent must be able to access the non-Oracle system using a non-Oracle system-specific communication mechanism.

When a user session accesses a non-Oracle system through a database link on the Oracle8 server, a Net8 Listener starts an agent process. This agent process remains running, until the user session is disconnected, or until the database link is explicitly closed.

#### Figure 6-1 Accessing Heterogeneous Non-Oracle Systems

### Process Architecture for Distributed External Procedures

Distributed external procedures map PL/SQL procedures onto remote 3GL routines that reside in a dynamic linked library (DLL). Whenever a distributed external procedure is executed, the agent will load the operating system dynamic linked library that contains the 3GL routine into the agent process, map the PL/SQL procedure onto the 3GL routine, and invoke the 3GL routine. After the 3GL routine finishes processing, the arguments and return values are passed back to the calling PL/SQL program. See Figure 6-2.

Note: On some platforms, dynamic linked libraries are referred to as shared libraries.

To access a non-Oracle system using a distributed external procedure , you need an agent specifically designed for that non-Oracle system . The agent contains non-Oracle system-specific code which sets up a session at the non-Oracle system, and integrates the transactions performed at non-Oracle system by the distributed external procedure into an Oracle distributed transaction.

#### Figure 6-2 Oracle8, agents and Dynamic Libraries

For example, you have an agent that provides access to a queuing system. To put a message into the queue, an Oracle application issues the following statement:

    SQL> EXECUTE enqueue@queuing_system('We are out of stock');
    SQL> COMMIT;

The enqueue procedure resides in a dynamic linked library. When you execute the statement above, the Net8 listener spawns the agent process. The agent process loads the DLL containing the enqueue procedure, and executes the enqueue procedure to put a message in the queuing system. When you COMMIT the transaction, the agent will ask the queuing system, on behalf of the Oracle server, to commit the transaction.

The agent process continues running for the duration of the Oracle user session, or until you close the database link explicitly by using the "ALTER SESSION CLOSE DATABASE LINK queing_system" command.

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index