Oracle Transparent Gateway

The Oracle transparent gateway allows Oracle client applications to access SQL Server. The gateway, in conjunction with an Oracle server, creates the appearance of all data residing on a local Oracle server, even though the data might be widely distributed. The Oracle transparent gateway provides:

• Transparent access to data.
• Heterogeneous transactions.
• Two-phase commit.
• Application portability.

The gateway accesses SQL Server data. Oracle client applications do not connect directly to the gateway, but connect indirectly to an Oracle server. The Oracle server communicates with the gateway by using SQL*Net. The gateway is started as a Windows NT-based service.

Client Applications

Client applications and Oracle tools, such as Oracle Forms, access the gateway through the Oracle server. When a client application queries a SQL Server database through the gateway, the query triggers the following sequence of events:

1. Client application sends a query to the Oracle server.
2. Oracle server sends the query to the gateway.
3. For the first transaction in a session, the gateway logs into SQL Server using a username and password that is valid in the SQL Server database.
4. Gateway converts the Oracle PL/SQL statement to a Transact-SQL statement understood by SQL Server.
5. Gateway retrieves the data using a Transact-SQL statement.
6. Gateway converts the retrieved data to a format compatible with Oracle.
7. Gateway returns the query results to the Oracle server.
8. Oracle server returns the query results to the client application.

Oracle Server

The Oracle server connects directly to the gateway, allowing heterogeneous queries against Oracle and SQL Server data. The Oracle server also post-processes Oracle SQL functions not supported by SQL Server. Definitions of database links for the SQL Server database are stored in the Oracle server.

SQL*Net

SQL*Net version 2.0 provides client-to-server and server-to-server communication. This allows a client to communicate with the Oracle server, and the Oracle server to communicate with the gateway.

The Oracle server and the gateway work together to present the appearance to the client of a single Oracle database.

Heterogeneous Database Integration

The Oracle server can accept an SQL statement that queries data stored in several different databases. The Oracle server passes the appropriate SQL statement directly to other Oracle databases and through gateways to other databases. The Oracle server then combines the results and returns them to the client, allowing a query to be processed that spans SQL Server and local and remote Oracle data.