====================================================== Oracle COM Automation for PL/SQL 11.2.0.1.0 Production ====================================================== Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved. This document provides information that supplements the Oracle COM Automation documentation. This document lists the steps to install and configure Oracle COM Automation Feature for PL/SQL. Oracle COM Automation Feature for PL/SQL includes the following components: * Oracle COM Automation Feature for PL/SQL (orawpcomVER.dll) * PL/SQL installation and definition script (comwrap.sql) * An Oracle database to Microsoft Word data exchange solution * An Oracle database to Microsoft Excel data exchange solution * An Oracle database to Microsoft PowerPoint 97 data exchange solution * An Oracle database to Extended MAPI e-mail solution Installation of Oracle COM Automation Feature for PL/SQL ======================================================== The following steps describe how to install Oracle COM Automation Feature for PL/SQL: 1. Open an MS-DOS command prompt. 2. Use the following command to change the directory to com: cd ORACLE_BASE\ORACLE_HOME\com 3. Connect to the database as system using SQL*Plus. For example: sqlplus system/manager 4. Grant the "Create Library" privilege to the database user(s) that will use Oracle COM Automation Feature for PL/SQL: grant create library to hr 5. Log in as the database user that will use Oracle COM Automation Feature for PL/SQL: connect hr/hr 6. Execute the comwrap.sql script that can be found in the com directory of the Oracle home, where Oracle COM Automation Feature for PL/SQL is installed: @comwrap.sql 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 can be safely ignored. External Procedure Configuration ================================ Since Oracle COM Automation Feature for PL/SQL relies on external procedure call -outs, it is imperative that the listener and Oracle Net Services 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 the following two possibilities: * The listener has not been started. The administrator can start the Oracle TNS Listener service either from the services control panel or from the command line (for example, net start OracleORCLTNSListner). * The listener has not been configured properly. In this case, the administrator must modify the listener.ora and tnsnames.ora files. 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. Refer to the Oracle COM Automation Developer's Guide or the Oracle Net Services 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 you an idea of how to use Oracle COM Automation Feature for PL/SQL to build solutions. These solutions are provided in the following ways: * As examples showing how to use Oracle COM Automation Feature for PL/SQL * As a foundation upon which you can build more customized, complex applications that use Oracle COM Automation Feature for PL/SQL 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 database 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 database and Microsoft Word. The WordDem.sql script exchanges data from the EMPLOYEES table in Oracle database to a Microsoft Word document. You should run this script after installing the solution. This demo relies on the HR schema in the common schema. Installing the Microsoft Word Solution ------------------------------------ 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. 1. Open an MS-DOS command prompt. 2. Use the following command to change the directory to com\demos directory. For example, cd ORACLE_BASE\ORACLE_HOME\com\demos 3. Start SQL*Plus and log in as the database user that will use the Microsoft Word Solution. For example, connect hr/hr 4. Execute the WordSol.sql script located in the ORACLE_BASE\ORACLE_HOME\com\demos directory: @WordSol.sql 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. 1. Open an MS-DOS command prompt. 2. Use the following command to change the directory to com\demos directory. For example, cd ORACLE_BASE\ORACLE_HOME\com\demos 3. Start SQL*Plus. sqlplus.exe is located in ORACLE_BASE\ORACLE_HOME\bin directory, if it is not in your path. 4. Log in as the database user that will use the Microsoft Word Solution. For example, connect hr/hr 5. Execute the WordDem.sql script located in the ORACLE_BASE\ORACLE_HOME\com\demos directory: @WordDem.sql This script will create a Word document (worddemo.doc) in the c:\. The document will contain data from the EMPLOYEES table. 6. Open the worddemo.doc file 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 database 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 database and Microsoft Excel. The ExcelDem.sql script exchanges data from the EMPLOYEES table and the JOBS table in Oracle database to a Microsoft Excel worksheet and graphs it. You should run this script after installing the solution. This demo relies on the HR schema in the common schema. Installing the Microsoft Excel Solution ----------------------------------------- The following steps detail how to install the Microsoft Excel Solution. 1. Open an MS-DOS command prompt. 2. Use the following command to change the directory to com\demos: cd ORACLE_BASE\ORACLE_HOME\com\demos 3. Start SQL*Plus. sqlplus.exe is located in ORACLE_BASE\ORACLE_HOME\bin directory, if it is not in your path. 4. Log in as the database user that will use the Microsoft Excel Solution. For example: connect hr/hr 5. Execute the Excelsol.sql script located in the ORACLE_BASE\ORACLE_HOME\com\demos directory: @ExcelSol.sql 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. 1. Open an MS-DOS command prompt. 2. Use the following command to change the directory to com\demos: cd ORACLE_BASE\ORACLE_HOME\com\demos 3. Start SQL*Plus. sqlplus.exe is located in ORACLE_BASE\ORACLE_HOME\bin directory, if it is not in your path. 4. Log in as the database user that will use the Microsoft Excel Solution. For example: connect hr/hr 5. Execute the ExcelDem.sql script located in the ORACLE_BASE\ORACLE_HOME\com\demos directory: @ExcelDem.sql This script will create an Excel spreadsheet (excelxxxxx.xls) in the c:\. The document will contain data from the EMPLOYEES table and the JOBS table in Oracle database. 6. Open the excelxxxxx.xls file, where xxxxx is a timestamp, 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 database to an Extended MAPI e-mail message. You should run this script after installing the solution. This demo relies on the HR schema in the common schema. Installing the MAPI Solution ------------------------------ 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. For example: DOMAIN1\userXYZ 2. Start a MAPI server, for example, Microsoft Outlook and configure the MAPI profile for NT user DOMAIN1\userXYZ. Ensure that you are able to send out e-mail using this profile. 3. Go to NT Control Panel/Services. 4. Shutdown Oracle TNS Listener Service. 5. Select Oracle TNS Listener Service and click "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 "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. Before installing the MAPI demo, you need to apply the following fixes: 1. Patch CDO.DLL as mentioned in the MSDN article, 268272. This patched DLL is part of Microsoft Exchange Server 5.5. Manually copy the patched DLL to the proper location. The default location for this DLL is: C:\Program Files\Common Files\System\Mapi\1033\NT 2. Update the registry settings by double-clicking MAPI.REG from the Windows Explorer. MAPI.REG is located in: ORACLE_BASE\ORACLE_HOME\com\demos The following steps detail how to install the MAPI Solution. 1. Open an MS-DOS command prompt. 2. Use the following command to change the directory to com\demos: cd ORACLE_BASE\ORACLE_HOME\com\demos 3. Start SQL*Plus. sqlplus.exe is located in ORACLE_BASE\ORACLE_HOME\bin directory, if it is not in your path. 4. Log in as the database user that will use the MAPI Solution. For example: connect hr/hr 5. Execute the MAPISol.sql script located in the ORACLE_BASE\ORACLE_HOME\com\demos directory: @MAPISol.sql 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. 1. Open an MS-DOS command prompt. 2. Use the following command to change the directory com\demos: cd ORACLE_BASE\ORACLE_HOME\com\demos 3. Open the MAPIDem.sql file with a text editor and change the e-mail address hr@us.oracle.com in ORDMapi.AddRecipient to your own e-mail address. If you are not using the default as your profile name, also change the profile name that is indicated in ORDMapi.CreateMAPISession in MS Exchange Settings. 4. Save the changes. 5. Start SQL*Plus. sqlplus.exe is located in ORACLE_BASE\ORACLE_HOME\bin directory, if it is not in your path. 6. Log in as the database user that will use the MAPI Solution. For example: connect hr/hr 7. Execute the MAPIDem.sql script located in the ORACLE_BASE\ORACLE_HOME\com\demos directory: @MAPIDem.sql This script will send an e-mail that lists all the employee names from the EMPLOYEES table in Oracle database. 8. Open your MAPI client to see the sent e-mail. Known Bugs ========== At present, you cannot call the COM Automation methods that display a window, message box, or dialog box. Workaround: ========== Avoid calling methods that display such dialog boxes. For example, the Microsoft Excel solution appends the time stamp to the filename (for example, 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.