Understand Oracle COM Automation for Windows

An Oracle database server running on a Windows machine can manipulate COM Automation server objects from PL/SQL or Java stored in a database.

To access a COM Automation server, a client application requests an instance of an object from the server that implements the IDispatch standard interface. Using this object, you can create instances of other objects and invoke methods on those objects with arguments.

An Oracle database on Windows can act as a COM Automation client by referencing orawpcom.dll through an external procedure callout, which maps stored procedure calls to DLL function calls. The script comwrap.sql will automatically create a package to wrap the external procedure calls. In order to install comwrap.sql under a schema, the schema must have CREATE LIBRARY privilege, which isn't granted by default to non-DBA accounts.

For example, if you want to enable COM callouts from the HR account (i.e., the one used by the COM Automation demos), you could run the following:

cd %ORACLE_HOME%\com
sqlplus "/ as sysdba"
SQL> grant create library to hr;
SQL> connect hr/hr
SQL> @comwrap

The last step creates several objects, including the PL/SQL package ORDCOM, which provides a slightly friendlier interface on COM interface calls. Using this package, you can create instances of COM Automation objects, invoke their methods, and reference their properties. For example, to create an instance of an Excel spreadsheet, use the following code:

SQL> variable hr number;
SQL> variable app number;
SQL> execute :hr := ordcom.CreateObject('Excel.Application',0,'',:app);

In the example, the variable hr contains the COM standard result number. If this value isn't 0 (success), you can examine the error returned with this syntax:

SQL> variable error_src varchar2(255);
SQL> variable error_description varchar2(255);
SQL> variable error_helpfile varchar2(255);
SQL> variable error_helpid number;
SQL> execute
ordcom.GetLastError(:error_src,:error_description,:error_helpfile,:
error_helpid);
SQL> print

The app variable will contain a handle to the application instance. If you look at your Task Manager window, you should see an instance of EXCEL.EXE running in the background. You can access a property from your COM object, such as the current WorkBooks collection with this code:

SQL> variable wbs number;
SQL> execute :hr := ordcom.GetProperty(:app,'WorkBooks',0,:wbs);

You can also invoke an objects method. This is how you would issue a Quit command to Excel when you finish using it:

SQL> variable dummy number;
SQL> execute :hr := ordcom.Invoke(:app,'Quit',0,:dummy);

You can also wrap each one of these functions in a PL/SQL package. You can find the demos that have been provided since Oracle8i (and still say Oracle8i in the 10g versions) in the Windows ORACLE_HOME/com/demos directory. (For Oracle 10g, you need to install the companion disc for Oracle 10g). There is a package called ORDExcel, created in the script excelsol.sql and a demo, which depends on the HR schemas tables in the script exceldem.sql. There are also demos for MAPI (the mail exchange API), which sends an e-mail, and Microsoft PowerPoint and Microsoft Word, which create documents using PL/SQL.

Since stored procedures are accessible to Java stored procedures, you can also write procedures in Java to access COM objects. There is a Java package stored in the Oracle database under oracle.win.com.* that wraps the COM external procedure DLL. To create a client handle to a COM automation server, simply create an instance of an Automation object. Then, you can access properties or invoke methods in the same way as PL/SQL.

import oracle.win.com.*;
. . .
Automation word = new Automation("Word.Basic");
word.InitArg();
word.Invoke("FileNewDefault");

word.InitArg();
word.SetArg("test.doc");
word.Invoke("FileSaveAs");
word.Destroy();
word = null;
. . .

There is a Java demo program that creates a Word document in %ORACLE_HOME%\com\java\demos\TestWORD.java. It includes a wrapper for a handful of Word interface calls, as well as a test program. However, none of the demo application wrappers are complete. If you want to be complete, you may want to investigate creating a program that interrogates the IDispatch interface of an object and automatically generates a PL/SQL or Java wrapper-based on the ORDCOM package.

Scott Stephens worked for Oracle for more than 13 years in technical support, e-commerce, marketing, and software development.