Oracle Tip: Consider using Oracle objects for OLE in Windows

Takeaway: This tip demonstrates how you can use the Oracle Objects for OLE (OO4O) In-Process Automation Server to connect to Oracle database servers and execute SQL or PL/SQL procedures through COM Automation Objects.

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 ourOracle Dev Tips Library.