[Laskey99] 5.4. Connecting to Non-Oracle Databases

5.4. Connecting to Non-Oracle Databases

Oracle provides several methodsfor connecting to databases provided by other vendors. Thesedatabases range from small, single-user databases like MicrosoftAccess to large, multi-user databases like Sybase or Microsoft SQLServer.

5.4.1. Open DataBase Connectivity

The Open DataBaseConnectivity (ODBC) is actually an Application Programming Interface(API) specification developed by Microsoft. Because ODBC provides astandard interface to both the application program and the backendserver, it allows any ODBC-compliant application to connect to anyODBC-compliant database. ODBC has been implemented for Oracle in twoways:

Native mode ODBC

The ODBCdriver duplicates the functionality of SQL*Net, so a client runningan ODBC driver can connect to a server running SQL*Net withoutactually running SQL*Net software on the client machine. Thus, theODBC driver is able to emulate SQL*Net. This capability is notprovided by Oracle, but is implemented by other vendors of ODBCdrivers.

SQL*Net integrated

The ODBC driver provides an interface toSQL*Net that, in turn, actually carries the data to the target node.This is the more common implementation of ODBC, where the ODBC layeractually sits on top of SQL*Net.

Because many applications have been built to interface with ODBC,this technology allows a wide range of applications to connect to anOracle database without the need to provide a native SQL*Netinterface. However, because ODBC relies on SQL*Net for its underlyingtransport, communications using ODBC are typically less efficientthan native SQL*Net. In addition, some Oracle functionality is notfully supported by ODBC drivers. This lack of functionality is easilymistaken for an application problem, and may be difficult to detectand correct. If full Oracle functionality is required, use the OracleCall Interface (OCI), rather than ODBC.

5.4.2. Gateways

Oracle hasresponded to the need for access to non-Oracle databases such asIBM's DB2 by developing gateway products. These gateways run asprocesses on the non-Oracle host machine, and essentially operate byemulating SQL*Net and an Oracle database on the non-Oracle host. Forexample, IBM provides a native SQL database on its AS/400 computers,which are very popular small office machines. Oracle does not have adatabase product that runs on AS/400, but it does provide an AS/400gateway. This gateway has a listener process that functions just likethe listener on any Oracle server, and is used to establishconnections to Oracle servers. Once a connection is established,gateway software translates Oracle SQL queries into native AS/400queries, and then returns the data to the Oracle server, essentiallymaking the data stream look as if it had come from another Oracleserver.

One restriction on the use of an Oracle gateway is that it can onlybe accessed from an Oracle server using a database link; an Oracleclient is not able to directly connect to a gateway.

Not all gateways support Oracle's two-phase commit mechanism for distributed transactions. If you require this functionality, consult the appropriate Oracle documentation for the gateway.

• 
• Create Bookmark (Key: b)Create Bookmark
• Create Note or Tag (Key: t)Create Note or Tag
• Download (Key: d)Download
• Email This Page (Key: e)Email This Page
• PrintPrint

• Html View (Key: h)Html View
• Zoom Out (Key: -)Zoom Out
• Zoom In (Key: +)Zoom In
• Toggle to Full Screen (Key: f)
• Previous (Key: p)Previous
• Next (Key: n)Next

Related Content

Converting ODBC applications
From: Migrating from Oracle . . . to IBM Informix Dynamic Server on Linux, Unix, and Windows

Configuring Oracle Net
From: Oracle Essentials, 4th Edition