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.

TechRepublic’s Oracle newsletter covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more. Automatically subscribe today!