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<version>.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%\bin 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 <oracle_home>\com\comwrap.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 SUPERMAN
identified by *********
default tablespace USERS
temporary tablespace TEMP
quota unlimited on USERS
/
grant connect, resource to SUPERMAN
/
grant create library to SUPERMAN
/
connect SUPERMAN/********

@?\com\comwrap.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%\network\admin.) 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_<listenername>) 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_<listenername> section, add:

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

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

SID_LIST_LISTENER =
  (SID_LIST =
     (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oracle\product\10.2.0)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (sid_name=mysid)
      (oracle_home = C:\oracle\product\10.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 <listenername>. (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))
    )
  )