Oracle Objects for OLE (OO4O)
The Oracle Objects for OLE (OO4O) wereintroduced 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.
Oracle Gateways
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.