Oracle Objects for OLE (OO4O) FAQ
$Date: 11-Jan-1997 $
$Revision: 1.0 $
$Author: Frank Naudé $
Topics
Back to Oracle FAQ Index
What is Oracle Objects for OLE?
Oracle Objects for OLE (OO4O) is a middleware product manufactured by
Oracle Corporation that allows native access to Oracle7 databases from
client applications via the Microsoft OLE (Object Linking and Embedding)
standard. OO4O's predecessor was called Oracle Glue.
Oracle Objects consists of the following three components:
- An OLE 2.0 Automation (InProcess) Server
- This provides an OLE Automation interface to applications that support
OLE automation scripting such as Visual Basic (VB)
- An Oracle Data Control
- This is the Visual Basic custom control (VBX)
- Two C++ Class Libraries
- C-Libraries for Microsoft Foundation Classes (MFC) and Borland (OWL)
Back to top of file
How does OO4O compare to ODBC?
OO4O provides native access to Oracle and only Oracle databases.
It is faster than ODBC access but one can use ODBC to connect to a variety
of data sources.
Since OO4O closely follows the ODBC query interface model, one can retain
over 95% code compatibility between the OO4O and ODBC versions
of the data layer procedures. This translates to very short retooling times
in porting between the two products.
Back to top of file
Can I use OO4O to access Oracle data from Microsoft applications?
Yes, you can use OO4O from any Microsoft application that uses the VB-style
macro language. This includes Visual Basic, Excel, and MS-Access.
Back to top of file
How does one connect to an Oracle Database?
Sub Form_Load ()
Dim OraSession As Object 'Declare variables as OLE Objects
Dim OraDatabase As Object
Dim OraDynaset As Object
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.DbOpenDatabase("SQL*Net_Connect_String", "scott/tiger", 0&)
MsgBox "Connected to " & OraDatabase.Connect & "@" & OraDatabase.DatabaseName
'Create the OraDynaset Object and display the first value
Set OraDynaset = OraDatabase.DbCreateDynaset("select empno, ename from emp", 0&)
MsgBox "Employee " & OraDynaset.Fields("empno").value & ", #" & OraDynaset.Fields("ename").value
End Sub
Back to top of file
What are the major VB/ODBC limitations that OO4O overcomes?
(From the Oracle Objects Technical Overview by Keith Majkut):
Visual Basic 3.0 database access is not very client/server oriented.
In fact, the Visual Basic database access is really designed for
flatfile databases, but Visual Basic can use ODBC to access SQL
databases. Unfortunately, Visual Basic, by default, makes use of
a local SQL engine, called JET to process queries (using the MS
JET SQL implementation, NOT Oracle's). You may bypass the JET
engine (using something called SQLPASSTHROUGH, which causes the
SQL to be sent directly to the database), but doing so causes
your dynaset to be read-only. You would then have to update data
by constructing SQL insert/delete/update statements. You would
not be able to use the built in AddNew, Delete and Update
methods. (Note: SQL statements cannot contain text data > 32K and
cannot contain binary data.)
The drawbacks of the VB/JET/ODBC combination are many:
- Rows are accessed via a primary key (unique index). Your table MUST
contain a unique index, or else the resulting dynaset will NOT be
updatable. This is an ODBC feature related to the fact that ODBC is
based on Microsoft's SQLServer.
- When a dynaset is created, only the primary keys are fetched; rows are
not fetched until they are needed. This sounds reasonable until you
realize that it results in multiple queries and many trips to the
server.
- When a snapshot is created, all rows are immediately fetched. This
could potentially be millions of rows.
- Table joins are performed locally.
- Although views and synonyms can be used, they will cause your dynaset
to be read-only.
- Column aliases and schema references (such as "SCOTT.EMP") cannot be
used without SQLPASSTHROUGH.
- Object names are referenced in UPPER case unless you use
SQLPASSTHROUGH. This only works for the (default) Oracle setting of
case-insensitive objects.
- There is no support for SQL bind variables (i.e. "SELECT * FROM EMP
WHERE ENAME = :name"). If the value of your variable changes, you need
to recreate the SQL statement and dynaset. This means that the server
must reparse the query and refetch the data.
- There is no support for PL/SQL bind variables (in/out parameters). The
workaround is to pass values in as literals, and store the return
values in a temporary table that you can then query on to get the
results.
- Multiple data controls (or database opens) use multiple database
connections, even if they all reference the same account on the same
database. This can be a serious problem with databases that have a
limit on the total number of connections they will accept.
Oracle Objects has no such drawbacks:
- Rows are accessed via their rowid. If a rowid can be obtained, then
the data is updatable.
- When creating a dynaset, some data is initially fetched and cached
locally. Data is fetched and stored locally as needed, as rows are
traversed. Admittedly, this can lead to a large local data cache, so
many tunable cache parameters are provided to improve performance.
- Snapshots have not been implemented, since a read-only dynaset is a
reasonable equivalent.
- Table joins are performed by the server.
- Views, synonyms, column aliases and schema references can be used
freely. The updatability of database objects is only dependent SQL
updatability rules and on the access you have been granted .
- Objects names are not modified in any way. You may use upper or lower
case in the names, which are case-insensitive.
- Support for SQL bind variables (i.e. "SELECT * FROM EMP WHERE ENAME =
:name") is offered via the Parameters collection. The server does not
have to reparse the query before refetching data.
- Support for PL/SQL bind variables is offered (in/out vars) via the
Parameters collection.
- Multiple data controls (or database opens) referencing the same
account on the same database will all share a single Oracle
connection.
At first, the VB/JET/ODBC limitations may not sound serious, but consider a
typical production Oracle environment where most users only have access to
data via synonyms and views. This means that VB/JET/ODBC users can only
read, not write data.
Back to top of file
Where can I get more info about OO4O?
Back to top of file