6
Understanding Oracle Heterogeneous Services

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

What is Heterogeneous Services?

Heterogeneous Services is an integrated component within the Oracle8 server and the enabling technology for Oracle's next generation of Open Gateway products. Heterogeneous Services provides the common architecture and administration mechanisms for future Oracle gateway products and other heterogeneous access facilities, while providing upwardly compatible functionality for users of earlier Oracle Open Gateway releases. To access a particular non-Oracle system, you will need a complementary product that implements the Heterogeneous Services agent functionality described in "Heterogeneous Services Process Architecture" on page 6-6.

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.

For both forms of heterogeneous access, Heterogeneous Services maintains session context for security and transaction context for reliability and protection against failures.

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

The Services provided by Heterogeneous Services

Heterogeneous Services provides two services:

SQL service
Procedural service

Both the procedural service and the SQL Service allow 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. A non-Oracle system can transparently participate in Oracle's two-phase commit protocol when an application commits a transaction if the application has changed data in the non-Oracle system, even if that non-Oracle system itself does not support two-phase commit. See "Transaction Capabilities of the Non-Oracle System" on page 7-10 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, on the fly, into a SQL dialect understood by the non-Oracle system
transform SQL requests on Oracle's data dictionary tables, on the fly, 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. However, 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. To be able to access non-Oracle systems, distributed external procedures provide additional capabilities. These capabilities are:

distributed external procedures enable the Oracle8 server to start authenticated sessions at the non-Oracle system and to coordinate 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.

As shown in Figure 6-1, an Oracle application is connected to a local Oracle Server. When the application issues Oracle SQL or PL/SQL statements, the local Oracle SQL or PL/SQL engine will process the request. 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.

For example, if a non-Oracle system is accessed, 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.

Figure 6-1: Database Links Between Oracle8 and a Non-Oracle System

Heterogeneous Services Process Architecture

An agent is required to access a particular non-Oracle system from an Oracle8 Server. Oracle Server communicates with the agent. The agent communicates with a particular non-Oracle system. Agents are provided by Oracle, but they can also be provided by third-party vendors.

As shown in Figure 6-2, agents can reside on the same machine as the non-Oracle system but are not required to. Thast is, agents can reside on the same machine as the Oracle8 Server, or they can reside on any machine residing between the Oracle8 Server and the non-Oracle system.

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.

Note: See your agent-specific documentation for information about how to install, configure, and administer agents.

Figure 6-2: 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-3.

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-3: Oracle8, agents and Dynamic Libraries

For example, you have an agent that enables access to queuing system X. To put a message in system X's queue, an Oracle application issues the following statement:

BEGIN

     enqueue@systemx('We are out of stock');

     commit;

END;

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 system X. After successful completion, the agent asks system X, 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 systemx" command.