This article originally appeared in the Oracle e-newsletter. Click here to subscribe automatically.

Oracle Objects for OLE (OO4O) is a set of
software layers designed to provide access to an Oracle database
from Microsoft COM Automation and ActiveX compatible languages such
as Visual Basic, Visual Basic for Applications (VBA), IIS Active
Server Pages (ASP), Windows Script Host, and Visual C++. These
layers were written directly against the Oracle Call Interface
(OCI), bypassing the standard ODBC, OLE DB, and ADO layers. While
this creates incompatibility with products using other databases,
it enables direct access and broader access to an Oracle
database.

Developers can use the OO4O In-Process
Automation Server to connect to Oracle database servers and execute
SQL or PL/SQL procedures through COM Automation Objects. You can
also use the In-Process Server by creating a COM object for a
session from its interface, “OracleInProcServer.XOraSession,” and
accessing subobjects, as shown in this example using Visual
Basic:

Set objSession =
CreateObject(“OracleInProcServer.XOraSession”)
Set objDatabase = objSession.OpenDatabase(“”,”scott/tiger”,0)

With a database object, you can query records
from the database or execute DDL or DML statements directly. For
example, here’s a VBA script that you can run from inside Microsoft
Excel to fetch all the data from the EMP table into worksheet
cells:

Sub GetEmployees()
    ‘ Use OO4O
    Set objSession =
CreateObject(“OracleInProcServer.XOraSession”)
    Set objDatabase =
objSession.OpenDatabase(“”, “scott/tiger”, 0)

    Sql = “select * from emp”

    Set oraDynaSet =
objDatabase.DBCreateDynaset(Sql, 0)

    If oraDynaSet.RecordCount > 0
Then
        oraDynaSet.MoveFirst

        For x = 0 To
oraDynaSet.Fields.Count – 1
            Cells(1,
x + 1) = oraDynaSet.Fields(x).Name
            Cells(1,
x + 1).Format = Bold
        Next

        For y = 0 To
oraDynaSet.RecordCount – 1
            For
x = 0 To oraDynaSet.Fields.Count – 1
                Cells(y
+ 2, x + 1) = oraDynaSet.Fields(x).Value
            Next

            oraDynaSet.MoveNext

        Next
    End If

    Set objSession = Nothing
    Set objDatabase = Nothing
End Sub

While it’s possible to fetch data from an
external datasource into an Excel spreadsheet through the
Data/External Datasource function, this macro has much more direct
control, allowing you to specify exactly how the data is read into
the spreadsheet; and, because the macro doesn’t use as many layers
to get to the Oracle database, it should be faster. It also
provides additional functionality, including the ability to store
and retrieve blobs (such as images).

The OO4O set also includes an Oracle Data
Control, which is an ActiveX control that can be bound to custom
controls, such as those used in Visual Basic which automatically
track data with user interface interactions.

OO4O also comes with an OLE C++ class library,
which helps provide similar access to Visual C++ developers and can
be bound to foundation classes.

Scott Stephens worked for Oracle for more than 13 years in technical support, e-commerce, marketing, and software development. For more of his Oracle tips, visit our Oracle Dev Tips Library.