Copy of "Accessing the Oracle Database from Excel Using Oracle Objects for OLE", published in Oracle Magazine Interactive http://www.oramag.com in June 1999

ORACLE8

Accessing the Oracle Database from Excel Using Oracle Objects for OLE

By Gerrit-Jan Linker

Most people know you can access Oracle databases through ODBC. But did you know that you can also use Oracle Objects for OLE (OO4O) to connect to Oracle from Windows-based applications without using complex APIs or external data drivers?

At SCT International, a subsidiary of SCT Corporation, we used Oracle Objects for OLE to build Oraxcel (Currently named SQL*XL), a powerful Oracle-to-Excel utility. After creating Oraxcel, we used it extensively in the internationalization of SCT's Banner system, a suite of customer-care and billing products for the utilities sector. SCT continues to use Oraxcel as the company extends its expertise into internationalizing software products at its new Language Centre.

Oracle Objects for OLE

OO4O is Oracle's OLE server software designed to deliver easy access to Oracle databases from Windows clients. It can be used within any development environment capable of including OCX controls (previously known as VBX controls), including Microsoft development environments such as MS-VC++, Visual J++, Visual Basic, FoxPro and even Microsoft Office's Excel and Word. Other compatible environments are C++Builder from Inprise (formerly Borland C++) and Microsoft Internet Explorer.

Developing the Tranlation Process at SCT

SCT's Customer Information System, Banner CIS, provides comprehensive customer accounting facilities for utilities providing water, gas, or electricity services. Banner CIS maintains records of customers, premises, services, accounts, meter readings, and more; and provides the means to record and bill for services in a cost-effective, efficient, and manageable way. Banner CIS runs on various platforms including but not limited to, Digital Equipment Corporation (DEC) Alpha, Sun Solaris, Hewlett-Packard HP-UX, and Windows NT.

The project team at SCT needed to translate the Banner system from English into various European languages. The team sought a generic approach to meet this requirement and developed appropriate software to capture in an Oracle database all the texts and captions used throughout the system database. The team sent the texts to a translation agency where they were translated into different languages. The agency returned the translated texts and the team then generated the system.

A major challenge in this process was creating an easy way to get the extracted data to translation agencies. Since it was not possible to deploy the actual Oracle database at an agency, the project team looked at writing a Visual Basic or an Access program to accomplish the task. The program needed to present the text with some additional details, such as the forms on which the text appears, if it appears on buttons or labels, etc. To assist the translators, we wanted the program to contain standard word-processing features such as sorting, printing, cut & paste, and so on. It soon became apparent that Excel had the necessary functionality.

To tailor Excel for our purposes, we decided to create a spreadsheet application designed to maintain data integrity and use Excel's useful features. We were especially interested in keeping the general Excel features such as text formatting, printing, and sorting. We created Excel macros to process the text and then update the database with the translations. The processing macros were designed to validate translated texts. For example, text on a button has a fixed length, so we created macros to make sure that the translated text was no longer than the original text.

The basic translation process involved reading all system text from the software into an Oracle database and pasting all that data into Excel. The Excel file was sent to the translators, who entered the translated texts next to the original English texts. When finished, they returned the Excel file to SCT where we transferred the translated texts from Excel into the database. The system was then regenerated, using the texts in the new language.

Oraxcel: Present and Future

Since the successful translation of the Banner system from English into Dutch, the macros we created continue to be used at SCT to move Oracle data into spreadsheets for analysis, development, and testing purposes. We made the tool easier to use by puttin g in dialog boxes that simplify certain tasks, such as logging in to Oracle or prompting the user for SQL commands. We developed several macros to perform common tasks such as changing the column width after fetching data and sorting fetched data according to column name.

We finally decided to create an add-in from the macros and screens and make it available from our Web site. The tool presently includes support for inserts and updates, performs PL/SQL, and can be used to look at the table definitions. Nevertheless, its best feature is still the capability to fetch data directly into spreadsheets. Oraxcel is currently used as a productivity tool by software testers and developers around the world.

Getting Started

To access your Oracle database using Excel, all you need is Excel Version 5 or above. Excel macros are actually written in Visual Basic. The easiest way to get started is to record a few macros and look at the generated code. Alternatively, you can write macros from scratch if you know the syntax. You can refer to Excel's Help file to read about Visual Basic programming; the syntax of OO4O is explained in its Help files.

Programming with Oracle Objects for OLE

It takes a few simple lines of code to connect to Oracle from Excel using OO4O. Listed below is sample code you can use to connect to Excel. Start by creating a new module in Excel (from Excel, click File > New and choose module) and insert the code.

Public objSession As Object 

Public objDataBase As Object



Sub ConnectToOracle()



'Create a reference to the OO4O dll

Set objSession = CreateObject("OracleInProcServer.XOraSession")



'Create a reference to my database

Set objDataBase = objSession.OpenDatabase( "MyOraDB","scott/tiger", 0&)



End Sub

Once you've obtained a reference to a database, you can submit and execute PL/SQL or SQL code by using the ExecuteSQL method. The example below shows you how to delete records from a table, using ExecuteSQL.

Dim strSQL As String



'delete all records from emp

strSQL = "delete from emp"



objDataBase.ExecuteSQL( strSQL )

Queries are slightly more complicated because you must deal with the returned data. The following example shows how to count the rows in the "emp" table and display the results in a dialog box (MsgBox).

 Dim strSQL As String

Dim strResult As String

Dim strCount As String

Dim OraDynaSet As Object



strSQL = "select count(*) from emp"



'Retrieve the results from Oracle

Set OraDynaSet = objDataBase.DBCreateDynaset(strSQL, 0&)



'Convert the result to String

strCount = CStr( OraDynaSet.Fields(0).Value ) 



'Prepare the message

strResult = "There are " & strCount & " employees in emp."



'Show the message

MsgBox strResult

Excel Programming

So far, we've looked at a few key OO4O commands. When creating a spreadsheet application, it is equally important to understand the Excel object structure. The spreadsheets and workbooks can be modified through the Excel objects.

The easiest way to learn about Excel programming is to record a macro and look at the generated code. The example below shows the code generated by Excel when changing the font of cell B3 to bold.

 Sub Macro1()



Range("B3").Select

Selection.Font.Bold = True



   End Sub

The Excel object structure is outlined in the Visual Basic section of Excel's help. The most important objects are shown in the table below. The definitions of the properties and methods for these objects can be found in the Help file, and you can refer to these objects from the macro code.

The example below shows a mixture of Excel and OO4O code. This code retrieves two columns from an employee table called emp and puts the results into a new worksheet. For simplicity, the checks for NULL values fetched and the checks on data types have been omitted.

Dim strSQL As String

Dim strResult As String

Dim OraDynaSet As Object

Dim i As Integer



'A dd a new worksheet to your current workbook

Worksheets.Add

               

'select the employee name and number

strSQL = "select ename, empno from emp"



'Retrieve the results from Oracle

Set OraDynaSet = objDataBase.DBCreateDynaset(strSQL, 0&) 



If OraDynaSet.RecordCount > 0 then

'There were records retrieved



OraDynaSet.MoveFirst



'Loop the recordset for returned rows

For i = 1 to OraDynaSet.RecordCount



'Put the results in column A and B

ActiveSheet.Cells(i,1) = OraDynaSet.Fields(0).Value 

ActiveSheet.Cells(i,2) = OraDynaSet.Fields(1).Value



OraDynaSet.MoveNext



Next i

  End If

As you can see, you can achieve Oracle connectivity from Excel quite easily. Since OO4O ships with all Oracle licenses, the software is readily available and is also distributed under Oracle's trial license from the Oracle Web site (www.oracle.com).

There are several books on the market that provide a good introduction for audiences of all skill levels. The information in the Excel Help files is a bit limited, but you can get around this limitation by simply recording a macro to automatically generate the macro code.

G. J. Linkeris an analyst programmer at SCT International and an associated consultant at Scientific Consultancy Services.