Oracle Objects for OLE (OO4O)

The Oracle Objects for OLE (OO4O) were introduced to capitalize on the huge success of the Microsoft COM (Component Object Model) standard. Implementing OO4O allowed COM-compliant applications to connect to the Oracle RDBMS directly, bypassing ODBC, and thus increasing efficiency and raw speed of the applications.

The OO4Os consist of an in-process OLE automation server (DLL), which provides an OLE/ActiveX interface to COM-compliant applications, specifically Visual Basic and Visual Basic for Application (VBA); OCX custom Data Control, to facilitate data manipulations; and two C++ class libraries — one for Microsoft Foundation Classes (MFC) specification and one for Borland (OWL).

Note

In Microsoft ActiveX world there is a notion of "early bound" and "late bound" objects. The former refers to the object's data type resolved at compilation thus requiring explicit reference to the library containing the class (from which object is to be created) to be added to the project. The latter allows for use of generic variables of Object type or Variant to be used in code, and their data type is resolved and assigned at run-time. Each of the methods has its advantages and disadvantages. Early bound objects are usually faster to load and execute since all dependencies were resolved at compile time, while being prone to "DLL Hell"–a scourge created by introducing Dynamic Link Libraries (DLL). Application compiled with one version of the library does not work with a different version of the same library. Late Bound objects are more immune to the DLL Hell (not completely, though), but are slower to load as all dependencies must be resolved in run-time.

The OO4Os are installed with Oracle Client. Once you've configured SQL*Net on the machine (see Oracle documentation for more information), you may use the interface to connect to the Oracle RDBMS. The following short example demonstrates the use of OO4O from within Microsoft Visual Basic 6.0:

' The
		following example assumes that Service Name 'ACME' ' was configured using the
		Oracle Net Configuration Assistant ' Please refer to the Oracle documentation
		for additional ' information on Oracle configuration ' ' declare object
		variables as VARIANT (VB default) ' alternatively, you may add reference to the
		Oracles ' ActiveX DLL and use strongly typed variables '(if you do, the code
		below cannot be used ' within "classic" Active Server Pages(ASP)) ' Dim
		objSession Dim objDB Dim objDynaset Dim objFields Dim strSQL ' ' create late
		bound Oracle session object ' Set objSession =
		CreateObject("OracleInProcServer.XOraSession") ' ' connect to the database '
		Set objDB = objSession.DbOpenDatabase("ACME", _ "ACME/ACME", 0&) ' '
		assemble SQL query ' strSQL = "SELECT * FROM customer" ' ' create OraDynaset
		object using the SQL statement ' Set objDynaset = objDB.CreateDynaset(strSQL,
		0&) ' ' get handle to the fields collection of the ' dynaset object ' Set
		objFields = objDynaset.Fields ' 'position the dynaset to the very first record
		' objDynaset.MoveFirst ' ' scroll the dynaset object ' Do While Not
		objDynaset.EOF ' ' display the value of the first field in the result set ' in
		a message box ' MsgBox objFields(1).Value ' 'move to the next record '
		objDynaset.MoveNext Loop

OO4O do offer an advantage over ODBC but are very similar to the OLEDB provider for Oracle; the latter offers the advantage of a more standard interface and naming conventions.

The Oracle Open Gateway interface is used to access data from non-Oracle databases like IBM DB2 UDB, Microsoft SQL Server, Sybase, and so on; it even provides limited capabilities to access nonrelational data sources.

There are four types of Oracle Open Gateways, presented in the table below:

Oracle Gateway	Uses
Transparent Gateways	The most commonly used type to link non-Oracle RDBMS; when used, they handle complexities of intercommunication, details of syntax, and so on. They also manage distributed queries and transactions (i.e., heterogeneous queries).
Procedural Gateways	Used to communicate with nonrelational data sources via mapping of the foreign database functions into custom Oracle PL/SQL procedures.
Access Managers	Used exclusively for IBM AS/400 and MVS platforms.
Oracle Replication Servers	Used for heterogeneous data replication.

The Oracle Open Gateways are available for every major database product, including Microsoft SQL Server and IBM DB2.