Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


Integrate COM Component Functionality into Your PL/SQL Codebase : Page 2

Oracle provides developers a simple API that enables them to interact with most Microsoft COM objects directly from PL/SQL. Learn how to install the COM option and tap into the functionality of pre-built COM components on your machine right now.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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.

Click to enlarge
Figure 1. Subset of the Excel Object Model Used in This Article


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:

declare --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; begin --Create the application object --and put its token in ApplicationHandle hresult:=ORDCOM.CreateObject('Excel.Application', 0, '',ApplicationHandle); --Invoke some methods --The following is equivalent to the pseudocode: --ResultHandle := ApplicationHandle.SomeMethodname hresult:=OrdCom.Invoke(ApplicationHandle, 'SomeMethodName',0,ResultHandle); --Invoke the "quit" method for Excel hresult:=OrdCom.Invoke(ApplicationHandle, 'Quit',0,DummyHandle); --Destroy the application object hresult:=OrdCom.Destroy(ApplicationHandle); end;

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:

hresult:=ORDCOM.CreateObject('Excel.Application', 0, servername,ApplicationHandle); IF (hresult!=0) THEN ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID); dbms_output.put_line ('Error creating application, aborting: ' || hresult); dbms_output.put_line(error_src); dbms_output.put_line(error_description); dbms_output.put_line(error_helpfile); reslt := hresult; return reslt; END IF;

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 to False'); hresult:=ordcom.SetProperty(ApplicationHandle, 'DisplayAlerts',false,'BOOL'); IF (hresult!=0) THEN ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID); dbms_output.put_line ('Error setting DisplayAlerts to false: ' || hresult); dbms_output.put_line(error_src); dbms_output.put_line(error_description); dbms_output.put_line(error_helpfile); END IF;

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.InitArg(); ORDCOM.SetArg(-4167,'I4'); --template argument hresult := ordcom.Invoke(WorkbooksHandle, 'Add',1,CurrWorkbookHandle);

(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.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date