And Now for the Fun Stuff
Your Oracle database server should now be set up to use COM components. Now you're ready to work an example using some of the COM components that come with Excel (see Sidebar 3: An Extremely Brief Rundown of the Excel Object Model
). This example is loosely based on the Excel demo that comes with the Oracle COM option, but it requires less schema objects, is less complicated, and is better commented.
Before you begin, please note that Excel's COM components aren't particularly easy to work with when you have no graphical context. For example, you could easily to cause your code to hang by doing something that Excel would normally respond to with a pop-up confirmation box, such as trying to "Save As" with an existing file name. Without any graphical context, the box can't pop up. So Excel hangs, and your code hangs. If you find yourself in this situation, use the Task Monitor to kill the hanging EXCEL.EXE process.
You'll need to have a copy of Excel installed on the database server, and the user under which the Oracle process runs must be able to start Excel. (In most installations, Oracle runs under SYSTEM, so this isn't a problem.) Since COM components will be run by EXTPROC0, they will be run by the user under which the Oracle process runs. This is definitely something to keep in mind from a security standpoint, since in Windows the Oracle database usually runs under a privileged account.
Figure 1 shows a summary of the Excel objects you'll use.
|Figure 1. Subset of the Excel Object Model Used in This Article|
The PL/SQL COM API
The ORDCOM package that you installed previously provides a simple API for interacting with COM objects, which requires three basic steps:
- Create the object with ORDCOM.CreateObject.
- Invoke methods with ORDCOM.Invoke.
- Destroy the object with ORDCOM.DestroyObject.
In order to use Excel's COM objects, create the Application object, invoke some of its methods, and then destroy it. The code will look something like this:
--OrdCom functions return the COM result code,
--customarily known as HRESULT, as binary_integer
hresult binary_integer := 0;
--Some variables to hold our object tokens
ApplicationHandle binary_integer := -1;
ResultHandle binary_integer := -1;
DummyHandle binary_integer := -1;
--Create the application object
--and put its token in ApplicationHandle
--Invoke some methods
--The following is equivalent to the pseudocode:
--ResultHandle := ApplicationHandle.SomeMethodname
--Invoke the "quit" method for Excel
--Destroy the application object
Displaying Error Information
The Microsoft COM API returns HRESULT error codes, which can be hard to find information on. Fortunately, the OrdCom package provides a function that gets error information from the appropriate helpfile. The GetLastError function takes four OUT variables and returns a HRESULT code of its own. For example:
IF (hresult!=0) THEN
dbms_output.put_line ('Error creating
application, aborting: ' || hresult);
reslt := hresult;
Annoyingly, I've found that GetLastError has to run right after the erroring OrdCom call or it returns an error of its own. So error-handling code, like that in the if/then block above, has to be repeated throughout the procedure; you can't move it off into an exception section as you normally would.
Getting and Setting Properties
Of course, there's more to the API than three functions. To begin with, interfaces have properties as well as methods. You can get and set properties through GetProperty and SetProperty, respectively. For example, the Application object has a property called DisplayAlerts, which controls whether or not those GUI confirmation boxes pop up. If it's set to False, Excel picks the default option and doesn't pop up the dialog box. You can set it to False with SetProperty as follows:
--Application.DisplayAlerts = False
dbms_output.put_line ('Setting DisplayAlerts
IF (hresult!=0) THEN
dbms_output.put_line ('Error setting
DisplayAlerts to false: ' || hresult);
Passing and Reading Parameters
Methods can have IN and OUT parameters, of course. Unfortunately, this is where Oracle's COM API becomes burdensomely clunky. Consider the following example, which invokes the Add method of a Workbooks object. The Add method takes one argument (the workbook template) and returns a workbook object. Workbook templates are numerical constants; you can choose from a handful of them. This example uses the xlWBATWorksheet template, which has a value of -4,167.
To do this in Visual Basic, you'd need something like the following line of code:
CurrWorkbookHandle := WorkBooksHandle.Add(xlWBATWorksheet)
In PL/SQL, however, you must first call InitArg() to initialize the arguments to whatever method you're about to invoke, set the arguments one by one with their data types, and then finally invoke:
ORDCOM.SetArg(-4167,'I4'); --template argument
hresult := ordcom.Invoke(WorkbooksHandle,
(Note: The easiest way to find the values of Excel constants like xlWBATWorksheet is to go into the VB Object Browser, as described previously, and search for the constant name. The constant's value will be shown in the bottom panel.)
This syntax isn't too onerous when only one argument is being passed. The real awkwardness comes when you have a method that takes a number of arguments. Each argument must be specified separately, in order. Thus, a simple VB command like this:
turns into a painful block of SetArg commands. I recommend that you comment each call to SetArg with the name of the corresponding parameter.
Similarly, you can read the values of Out parameters after a method is invoked by calling InitOutArg followed by the appropriate number of GetArg commands.