Oracle COM Automation Feature for PL/SQL README

************************************************************************

Copyright (C) 2000 Oracle Corporation, All rights reserved.


This document discusses the necessary steps that must be followed in order to 
install and configure the COM Automation Feature for PL/SQL.  For the most 
up-to-date information, please see Oracle COM Automation Developer's Guide.

Oracle COM Automation Feature for PL/SQL includes the following components:

* COM Automation Feature for PL/SQL (orawpcom.dll)
* PL/SQL installation and definition script (comwrap.sql)
* An Oracle to Microsoft Word data exchange solution
* An Oracle to Microsoft Excel data exchange solution
* An Oracle to Microsoft PowerPoint 97 data exchange solution
* An Oracle to Extended MAPI email solution

System Requirement
===================
The Oracle COM Automation Feature requires the following:

* Oracle 8i Database or later
* Windows NT 4.0 or Windows 2000

Installation
============
The following steps detail how to install the COM Automation Feature 
for PL/SQL.

* Open a MS-DOS command prompt
* Change the directory to com (e.g. cd ORACLE_BASE\ORACLE_HOME\com)
* Start sqlplus and log in as internal to the appropriate instance.
  (e.g. connect as system)
* Grant the create library privilege to the database user(s) that will use 
  the COM Automation Feature for PL/SQL (e.g. grant create library to hr).
* Log in as the database user that will use the COM Automation Feature 
  for PL/SQL (e.g. connect hr/hr).
* Execute the comwrap.sql script (e.g. @comwrap.sql).  The comwrap.sql 
  script can be found in the com directory of the Oracle home in which the 
  COM Automation Feature for PL/SQL was installed.  This script will create 
  the ORDCOM package in the current user's schema.  You will receive several 
  "ORA-04043: object XXXX does not exist" when you execute this script for 
  the first time.  These errors are normal.

External Procedure Configuration
================================
Since the COM Automation Feature for PL/SQL relies on external procedure 
call outs, it is imperative that the listener and Oracle Net8 RPC mechanism 
be configured properly for this Feature to work.

An "ORA-28575: unable to open RPC connection to external procedure agent" 
error indicates one of two following possibilities.  The first possibility
is that the listener has not been started.  The administrator can start
the Oracle TNS Listener service from the services control panel or start
it from the command line (e.g. net start OracleOraHome81TNSListner).  The 
second possibility is that the listener is not configured properly.  In 
this case, the administrator must modify the listener.ora and tnsnames.ora 
files.

The following are examples that demonstrate how to configure the 
listener.ora and tnsnames.ora files to use inter-process communication 
(IPC) to invoke external stored procedures.  Please refer to the Oracle 
COM Automation Developer's Guide or the Net8 Administrator's Guide for 
additional information on how to configure the listener.ora and 
tnsnames.ora files for external procedures. 

listener.ora configuration file:

LISTENER =
  (ADDRESS_LIST =
        (ADDRESS=
          (PROTOCOL= IPC)
          (KEY= EXTPROC0)
        )
)
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = OFF
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = ORCL)
    )
    (SID_DESC =
      (SID_NAME = extproc)
      (PROGRAM=extproc)
    )
  )
PASSWORDS_LISTENER = (oracle)

tnsnames.ora configuration file:

extproc_connection_data.world =
  (DESCRIPTION =
    (ADDRESS =
        (PROTOCOL=IPC)
          (KEY=EXTPROC0)
    )
    (CONNECT_DATA = (SID=extproc)
    )
  )

Solutions for Oracle COM Automation Feature for PL/SQL
======================================================
As part of Oracle COM Automation Feature for PL/SQL, several solutions 
are provided to give developers an idea of how to use the COM Automation 
Feature for PL/SQL to build solutions.  These solutions provide base 
functionality.  They are provided as both examples as to how to use the 
COM Automation Feature for PL/SQL and as a foundation upon which to build 
more customized, complex applications that use COM Automation.


Microsoft Word Solution
-----------------------
The following sections detail how to install the Microsoft Word Solution 
and describe the Application Programming Interfaces (APIs) that it exposes.  
This solution is provided as an example of the types of solutions that can 
be built with Oracle and Microsoft Word.

The Microsoft Word Solution provides a PL/SQL package (ORDWord) that exposes 
several APIs for manipulating Microsoft Word.  Also, the Microsoft Word 
Solution includes a script to demonstrate the capabilities of exchanging 
data between Oracle and Microsoft Word.  The WordDem.sql script exchanges 
data from the employees table in Oracle to a Microsoft Word document.  A 
developer should run this script after installing the solution.

This demo relies on the human resources schema in the common schema.

Microsoft Word Solution Installation

The following steps detail how to install the Microsoft Word Solution.  
Microsoft Word must be installed on the local machine for this solution to 
work properly.

* Open a MS-DOS command prompt
* Change to the com\demos directory 
  (e.g. cd ORACLE_BASE\ORACLE_HOME\com\demos)
* Start sqlplus and log in as the database user that will use the Microsoft Word 
  Solution (e.g. connect hr/hr).
* Execute the WordSol.sql script (e.g. @WordSol.sql) located in the 
  ORACLE_BASE\ORACLE_HOME\com\demos directory.  This script will create 
  the ORDWord package in the current user's schema.  You will receive 
  several "ORA-04043: object XXXX does not exist" when you execute this 
  script for the first time.  These errors are normal.

Using the Microsoft Word Solution

The following steps detail how to execute the Microsoft Word solution.

* Open a MS-DOS command prompt
* Change to the com\demos directory 
  (e.g. cd ORACLE_BASE\ORACLE_HOME\com\demos)
* Start sqlplus (sqlplus.exe is located in ORACLE_BASE\ORACLE_HOME\bin 
  directory, if it is not in your path).
* Log in as the database user that will use the Microsoft Word Solution 
  (e.g. connect hr/hr).
* Execute the WordDem.sql script (e.g. @WordDem.sql) located in the 
  ORACLE_BASE\ORACLE_HOME\com\demos directory.  This script will create a 
  Word document (worddemo.doc) in the c:\.  The document will contain data 
  from the employees table.
* Open the worddemo.doc to see its content.


Microsoft Excel Solution
------------------------
The following sections detail how to install the Microsoft Excel Solution 
and describe the Application Programming Interfaces (APIs) that it exposes.  
This solution is provided as an example of the types of solutions that can 
be built with Oracle and Microsoft Excel.

The Microsoft Excel Solution provides a PL/SQL package (ORDExcel) that 
exposes several APIs for manipulating Microsoft Excel.  Also, the Microsoft 
Excel Solution includes a script to demonstrate the capabilities of 
exchanging data between Oracle and Microsoft Excel.  The ExcelDem.sql 
script exchanges data from the employees, and jobs tables in Oracle to a 
Microsoft Excel worksheet and graphs it.  A developer should run this script 
after installing the solution.

This demo relies on the human resources schema in the common schema.

The Microsoft Excel Solution Installation

The following steps detail how to install the Microsoft Excel Solution.

* Open a MS-DOS command prompt
* Change to the com\demos directory 
  (e.g. cd ORACLE_BASE\ORACLE_HOME\com\demos)
* Start sqlplus (sqlplus.exe is located in ORACLE_BASE\ORACLE_HOME\bin 
  directory, if it is not in your path).
* Log in as the database user that will use the Microsoft Excel Solution 
  (e.g. connect hr/hr).
* Execute the Excelsol.sql script (e.g. @ExcelSol.sql) located in the 
  ORACLE_BASE\ORACLE_HOME\com\demos directory.  This script will create 
  the ORDExcel package in the current user's schema.  You will receive 
  several "ORA-04043: object XXXX does not exist" when you execute this 
  script for the first time.  These errors are normal.

Using the Microsoft Excel Solution

The following steps detail how to execute the Microsoft Excel solution.

* Open a MS-DOS command prompt
* Change to the com\demos directory 
  (e.g. cd ORACLE_BASE\ORACLE_HOME\com\demos)
* Start sqlplus (sqlplus.exe is located in ORACLE_BASE\ORACLE_HOME\bin 
  directory, if it is not in your path).
* Log in as the database user that will use the Microsoft Excel Solution 
  (e.g. connect hr/hr).
* Execute the ExcelDem.sql script (e.g. @ExcelDem.sql) located in the 
  ORACLE_BASE\ORACLE_HOME\com\demos directory.  This script will create 
  an Excel spreadsheet (excelxxxxx.xls) in the c:\.  The document will 
  contain data from the employees, and jobs tables.
* Open the excelxxxxx.xls, where xxxxx is a timestamp, to see its content.


Microsoft PowerPoint 97 Solution
--------------------------------
The following sections detail how to install the Microsoft PowerPoint 97 
Solution and describe the Application Programming Interfaces (APIs) that it 
exposes.  This solution is provided as an example of the types of solutions 
that can be built with Oracle and Microsoft PowerPoint.

The Microsoft PowerPoint 97 Solution provides a PL/SQL package (ORDPPT) 
that exposes several APIs for manipulating Microsoft PowerPoint 97.  Also, 
the Microsoft PowerPoint 97 Solution includes a script to demonstrate the 
capabilities of exchanging data between Oracle and Microsoft PowerPoint.  
The PPTDem.sql script exchanges data from the employees table in Oracle 
to a Microsoft PowerPoint 97 slide.  A developer should run this script 
after installing the solution.

Note: There are small discrepancies among PowerPoint Object Models for
Office 95, 97 and 2000. This particular demo is designed to run with 
PowerPoint 97.

This demo relies on the human resources schema in the common schema.

The Microsoft PowerPoint 97 Solution Installation

The following steps detail how to install the Microsoft PowerPoint 97 
Solution.

* Open a MS-DOS command prompt
* Change to the com\demos directory 
  (e.g. cd ORACLE_BASE\ORACLE_HOME\com\demos)
* Start sqlplus (sqlplus.exe is located in ORACLE_BASE\ORACLE_HOME\bin 
  directory, if it is not in your path).
* Log in as the database user that will use the Microsoft PowerPoint 97 
  Solution (e.g. connect hr/hr).
* Execute the PPTSol.sql script (e.g. @PPTSol.sql) located in the 
  ORACLE_BASE\ORACLE_HOME\com\demos directory.  This script will create 
  the ORDPPT package in the current user's schema.  You will receive 
  several "ORA-04043: object XXXX does not exist" when you execute this 
  script for the first time.  These errors are normal.

Using the Microsoft PowerPoint 97 Solution

The following steps detail how to execute the Microsoft PowerPoint 97 
solution.

* Open a MS-DOS command prompt
* Change to the com\demos directory 
  (e.g. cd ORACLE_BASE\ORACLE_HOME\com\demos)
* Start sqlplus (sqlplus.exe is located in ORACLE_BASE\ORACLE_HOME\bin 
  directory, if it is not in your path).
* Log in as the database user that will use the Microsoft PowerPoint 97 
  Solution (e.g. connect hr/hr).
* Execute the PPTDem.sql script (e.g. @PPTDem.sql) located in the 
  ORACLE_BASE\ORACLE_HOME\com\demos directory.  This script will create 
  a PowerPoint presentation (PPTDemo.ppt) in the c:\.  The document will 
  list the names of all the employees.
* Open the PPTDemo.ppt to see its content.


MAPI Solution
-------------
The following sections detail how to install the MAPI Solution and describe 
the Application Programming Interfaces (APIs) that it exposes.  This solution 
is provided as an example of the types of solutions that can be built with 
Oracle and Extended MAPI client.

The MAPI Solution provides a PL/SQL package (ORDMAPI) that exposes several 
APIs for manipulating  Extended MAPI client.  Also, the MAPI Solution 
includes a script to demonstrate the capabilities of exchanging data between 
Oracle and an Extended MAPI client.  The MAPIDem.sql script exchanges data 
from the employees table in Oracle to an Extended MAPI email message.  A 
developer should run this script after installing the solution.

This demo relies on the human resources schema in the common schema.

The MAPI Solution Installation

The MAPI Solution invokes Extended MAPI client on the behalf of Oracle 
Database. Oracle Database Service on NT, by default, is running as NT system 
user LocalSystem. MAPI profile for user LocalSystem cannot be easily
configured. Before using the MAPI Solution, you should change both NT Oracle
Database Service and NT Oracle TNSListener Service to start up using a
Windows NT login user account:

1. Log on to NT using your local user account or domain user account 
   (e.g. DOMAIN1\userXYZ)
2. Start MAPI server (e.g. Microsoft Outlook) and configure the MAPI profile 
   for NT user DOMAIN1\userXYZ. Ensure that you are able to send out email 
   using this profile.
3. Go to NT Control Panel/Services.
4. Shutdown Oracle TNS Listener Service
5. Select Oracle TNS Listener Service and click on "Startup..."
6. Change "Log On As" to "This Account" and fill in NT user DOMAIN1\userXYZ.
7. Fill in the password and confirm password for NT user DOMAIN1\userXYZ. 
8. Restart Oracle TNS Listener Service
9. Shutdown Oracle Database Service
10. Select Oracle Database Service and click on "Startup..."
11. Change "Log On As" to "This Account" and fill in NT user DOMAIN1\userXYZ.
12. Fill in the password and confirm password for NT user DOMAIN1\userXYZ. 
13. Restart Oracle Database Service

The following steps detail how to install the MAPI Solution.

* Open a MS-DOS command prompt
* Change to the com\demos directory 
  (e.g. cd ORACLE_BASE\ORACLE_HOME\com\demos)
* Start sqlplus (sqlplus.exe is located in ORACLE_BASE\ORACLE_HOME\bin 
  directory, if it is not in your path).
* Log in as the database user that will use the MAPI Solution 
  (e.g. connect hr/hr).
* Execute the MAPISol.sql script (e.g. @MAPISol.sql) located in the 
  ORACLE_BASE\ORACLE_HOME\com\demos directory.  This script will create 
  the ORDMAPI package in the current user's schema.  You will receive 
  several "ORA-04043: object XXXX does not exist" when you execute this 
  script for the first time.  These errors are normal.

Using the MAPI Solution

The following steps detail how to execute the MAPI solution.

* Open a MS-DOS command prompt
* Change to the com\demos directory 
  (e.g. cd ORACLE_BASE\ORACLE_HOME\com\demos)
* Open file MAPISol.sql with a text editor and change email address 
  hr@us.oracle.com in ORDMapi.AddRecipient to your own email address. 
  Save the change.
* Start sqlplus 
  (sqlplus.exe is located in ORACLE_BASE\ORACLE_HOME\bin directory, 
  if it is not in your path).
* Log in as the database user that will use the MAPI Solution 
  (e.g. connect hr/hr).
* Execute the MAPIDem.sql script (e.g. @MAPIDem.sql) located in the 
  ORACLE_BASE\ORACLE_HOME\com\demos directory.  This script will send 
  an email that lists all the employee names from employees table.
* Open your MAPI client to see the sent email.

Known Bugs
==========
At present, a developer cannot call COM Automation method that displays a 
window, message box, or dialog box.  

The workaround is to avoid calling methods that display such dialog boxes.  
For example, the Microsoft Excel solution appends the time stamp to the 
filename (e.g. excelxxxxx.xls) to ensure that no two files are named the 
same.  Excel displays an overwrite dialog box, if a file has the same name, 
when you attempt to save it using COM Automation.  Microsoft Word does not 
do this.