racle’s COM automation feature lets Oracle PL/SQL or Java developers leverage any of the thousands of pre-existing COM components, as well as any pre-built, in-house COM components (See Sidebar 1: A Primer on COM). This article first demonstrates an install of the COM option, and then works an example using some of the Microsoft Excel functionality exposed as COM interfaces.

Installing the COM Option

For this step, you’ll need access to an Oracle database server running version 8.1.5 or higher of the Enterprise Edition server software, as well as the ability to install components of the server software. The Oracle database server must be running on a Windows platform.

The COM option for PL/SQL consists of the following:

• A .dll file (orawpcom.dll)
• The ORDCOM package and its supporting types (installed by comwrap.sql)
• Demo programs
• Message files (such as comus.msb)

All the components except the DLL will be installed in the %ORACLE_HOME%com directory. The DLL will be installed in the %ORACLE_HOME%in directory (See Sidebar 2: DLL Hell).

To install and configure the COM automation option for PL/SQL, you take the following three steps:

1. Install the Oracle COM option.
2. Configure COM automation for PL/SQL.
3. Configure the Oracle Listener by configuring the listener.ora and tnsnames.ora files.

Before you begin, please note that these installation and configuration instructions were designed to configure an Oracle 10gR2 Enterprise Edition database. Previous releases of Oracle may require minor variations.

Installing the Oracle COM Option

If you don’t already have the Oracle server software installed on your Windows machine, don’t worry; installing the COM component is simple. Just make sure to select “Custom” as the installation type. Then, when presented with a list of options that can be installed, make sure that “COM Automation” is selected.

If you already have the Oracle database server software installed, you simply need to add the COM component to your installation as follows:

1. Shut down all Oracle services in the affected Oracle Home.
2. Insert the software Disk 1 CD and run the Oracle Universal Installer found on it.
3. Select the Oracle Home you want to have COM automation.
4. Choose a “custom” install if prompted for install type.
5. When you are presented with a list of options you can install, make sure that “COM Automation” is selected.

Configuring COM Automation for PL/SQL

Now that you have the COM server software installed, you must install the ORDCOM package. Follow these three steps:

1. Choose which user will own the APIs for COM automation.
2. Log in as SYSTEM and grant CREATE LIBRARY to that user.
3. Log in as that user and run the script comcomwrap.sql.

For demonstration’s sake, the following example creates a user, SUPERMAN, and sets that user up to use COM automation. First, start up SQLPLUS and connect as the SYSTEM user. Then run the following commands (replacing the ***‘s with the appropriate passwords and the tablespace names with the appropriate tablespace names):

connect system/********create user SUPERMANidentified by *********default tablespace USERStemporary tablespace TEMPquota unlimited on USERS/grant connect, resource to SUPERMAN/grant create library to SUPERMAN/connect SUPERMAN/********@?comcomwrap.sql

You’ll get several “ORA-04043: object XXXX does not exist” messages when you run this script for the first time. This is normal. The script tries to drop each object before it creates it, in case you had an old version already in your database. Ignore these errors.

Note: If you want to write stored procedures that use COM automation but are owned by a different user than the one you created above, you will have to grant that user EXECUTE on the ORDCOM package. For example, to let the user SUPERDOG create stored procedures that use COM automation:

connect SUPERMAN/*******grant execute on ORDCOM to SUPERDOG;

Configuring the Listener

This step has two parts: configuring the listener.ora file and configuring the tnsnames.ora file. (Your listener.ora and tnsnames.ora files are both in %ORACLE_HOME%
etworkadmin.) You must enable the RPC (remote procedure call) mechanism by putting it in the listener.ora and tnsnames.ora files. In 10gR2, this feature is enabled by default when you create a database with the COM automation feature, but if you don’t have it enabled, the following sections explain what to do.

Note: If you’re not the DBA responsible for this database, be sure to let the DBA know you’re enabling the RPC mechanism, otherwise known as “extproc”. Several Oracle security vulnerabilities center around this mechanism, so it’s important that the DBA know it’s running.

Configuring the listener.ora File

The listener.ora file provides configuration information for the Oracle Listener. The Listener is a separate process that runs on your server. Its sole function is to listen for database connection requests and pass them off to the databases. Essentially, you need to configure it to listen for RPCs and pass them off to the RPC handler.

The listener.ora file will have both a LISTENER= and a (SID_LIST_) section. You need to add something to both of these sections (if not already present.)

To the ADDRESS_LIST section, add:

(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0))

For example:

LISTENER =  (DESCRIPTION_LIST =    (DESCRIPTION =      (ADDRESS = (PROTOCOL = TCP)                 (HOST = myserver)(PORT = 1521))      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))    )  )

To the SID_LIST_ section, add:

(SID_DESC = (SID_NAME = plsextproc)(PROGRAM=extproc)). 

You can also add an (ORACLE_HOME=) key-value pair to this SID_DESC. For example:

SID_LIST_LISTENER =  (SID_LIST =     (SID_DESC =      (SID_NAME = PLSExtProc)      (ORACLE_HOME = C:oracleproduct10.2.0)      (PROGRAM = extproc)    )    (SID_DESC =      (sid_name=mysid)      (oracle_home = C:oracleproduct10.2.0)    )  )

You will need to reload your listener after you do this. The easiest way is to use LSNRCTL. Go to the command or shell prompt and type LSNRCTL RELOAD . (If you’ve set a password on your listener, you’ll need to provide that first.) Or, you can go to the Services panel and kick the Oracle Listener service.

Configuring the tnsnames.ora File

The tnsnames.ora file is like the Oracle version of a “hosts” file, or in less technical terms, like a phonebook. It lists database identifiers like “prod.world” and pairs them with the info Oracle needs to make the connection. This info points to the listener on the server where your database is running and provides the SID or service_name that will let the listener determine which database on that server to pass your connection request to.

You need to add a specific identifier and a set of connect information for the RPCs:

EXTPROC_CONNECTION_DATA=  (DESCRIPTION=    (ADDRESS=        (PROTOCOL=IPC)        (KEY=EXTPROC0)    )    (CONNECT_DATA=(SID=plsextproc))    )  )

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:

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:

Workbook.SaveAs('myfile.xls',xlCSVMSDOS,user_password,user_password,false,false)

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.

Let’s Get the Database in Play

There’s not much point in running COM automation through PL/SQL if you aren’t going to make use of the database. So let’s put a few values from the database into an Excel spreadsheet. The following example uses the EMP table found in the SCOTT schema, which you can load into your current schema by running the CREATE TABLE and INSERT statements in %ORACLE_HOME%/rdbms/admin/SCOTT.SQL or UTLSAMPLE.SQL.

First, work up a function to insert data into a cell. Assume that the token for the current worksheet is in CurrSheetHandle. You’ll want to do something roughly equivalent to this line of pseudocode:

   CurrSheetHandle.Range(cell_range).Value := data

Since the Oracle API won’t let you call a method of a method’s return value, you’ll have to do it in two steps, equivalent to the following:

  RangeHandle := CurrSheetHandle.Range(cell_range)  RangeHandle.Value := data

Here is the full function:

function InsertDataToRange ( cell_range varchar2,                              data varchar2)return binary_integeris    reslt binary_integer := 0;    rangeHandle binary_integer := -1;begin  --RangeHandle := CurrSheetHandle.Range(cell_range)      ORDCOM.InitArg();  ORDCOM.SetArg(cell_range, 'BSTR');  hresult:=ordCom.GetProperty(CurrSheetHandle,           'Range', 1, RangeHandle);  --RangeHandle.Value := data  hresult:=Ordcom.SetProperty(RangeHandle, 'Value',           data, 'BSTR');  if hresult != 0 then    . . .   end if;  hresult:=OrdCom.DestroyObject(RangeHandle);  RETURN reslt;END InsertDataToRange;

To call the function, you’d say something like this:

reslt := InsertDataToRange('A1','Shoes');

So to insert data from the EMP table into the current worksheet, you can simply open a cursor on the EMP table and insert its contents into the spreadsheet row by row:

declare  i binary_integer;  cursor empCur is    select * from emp order by ename;  xlRowIndex number;begin  i := xlDemo.InsertDataToRange('A1', 'Emp Name');  i := xlDemo.InsertDataToRange('B1', 'Emp Title');  --Insert employee last name and title for each row  --start inserting at row 2 in the excel spreadsheet  xlRowIndex := 2;  for emp in empCur   loop    i := xlDemo.InsertDataToRange('A' || xlRowIndex,                                  emp.ename);    i := xlDemo.InsertDataToRange('B' || xlRowIndex,                                  emp.job);    xlRowIndex := xlRowIndex + 1;    end loop;end; 

Putting It All Together

In Listing 1, I’ve created a package that puts all of the preceding examples together to create five functions: StartExcel, CreateAWorkbook, InsertDataToRange, SaveCurrWorkbook, and QuitExcel. The procedure RunDemo calls these in order to complete the following:

1. Start Excel.
2. Create a new workbook and create a new worksheet in that workbook.
3. Call InsertDataToRange in a loop as above, to insert data from EMP into the current worksheet.
4. Save the workbook.
5. Quit Excel.

Listing 2 holds sample output from the XLDemo.RunDemo procedure.

Tap into All Those COM Objects

Thanks to Oracle’s COM automation feature you can integrate the functionality of literally thousands of pre-built COM components with your PL/SQL codebase. Any Windows machine contains hundreds of COM objects, many of them documented online, and Oracle provides several COM demos, which you can find in the directory %ORACLE_HOME%comdemo. Now that you’ve seen how to install the COM automation option for PL/SQL and use it to interact with COM objects, you can fire up your database and start playing!