Oracle Objects for OLE (OO4O)

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).


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.