4 Oracle COM Automation PL/SQL Demos

This chapter describes how to use Oracle COM Automation Feature demonstration programs for PL/SQL.

This chapter contains these topics:

Overview of Oracle COM Automation Feature for PL/SQL Demos

Oracle COM Automation Feature for PL/SQL includes examples that demonstrate how to use the feature to build solutions. These demos provide base functionality and can serve as a foundation on which to build more customized, complex applications that use COM Automation. The demos are based on the human resources schema available with the sample schema.

Each demo exposes a core set of APIs that enables you to do simple operations using COM Automation. Each COM Automation server, such as Word and Excel, provides more advanced capabilities than what is offered through the demo APIs. To take advantage of these advanced features, you must design and code your own PL/SQL procedures.

In this release, COM Automation has provided the following demos:

  • Microsoft Word Demo - Exchanges data between Microsoft Word and Oracle Database

  • Microsoft Excel Demo - Exchanges data between Microsoft Excel and Oracle Database

  • Microsoft PowerPoint Demo - Exchanges data between Microsoft PowerPoint and Oracle Database

  • MAPI Demo - Exchanges data between Messaging Application Programming Interface (MAPI) compliant applications and Oracle Database

Microsoft Word Demo

The following sections describe how to install the Microsoft Word demo and the APIs that it exposes. This demo is provided as an example of the types of solutions that can be built with Oracle Database and Microsoft Word.

The demo creates a Microsoft Word document containing the names of employees in the database.

The Microsoft Word demo provides the following:

  • ORDWord, a PL/SQL package that exposes several APIs for manipulating Microsoft Word. This package is created by the wordsol.sql script.

  • worddem.sql, a script that displays the capabilities of exchanging data between Oracle Database and Microsoft Word. It exchanges data from the EMPLOYEES and JOBS tables to a Microsoft Word document. These tables are available in the human resources schema in the sample schema.

Installing the Microsoft Word Demo

Microsoft Word must be installed on the local computer before you install this demo.

To install Microsoft Word demos:

  1. Start SQL*Plus.

    C:\> sqlplus /NOLOG

  2. Connect to the Oracle database instance as the user who will use the Microsoft Word demo. For example:

    SQL> connect hr
Enter password: password

  3. Run the wordsol.sql script at the SQL*Plus prompt:

    SQL> @ORACLE_BASE\ORACLE_HOME\com\demos\wordsol.sql;

    This script creates the ORDWord package in the current user's schema. You will receive the following error several times when you run this script for the first time:

    ORA-04043: object XXXX does not exist.

    These messages are normal.

Using the Microsoft Word Demo

To use the Microsoft Word demo:

  1. Run the worddem.sql script at the SQL*Plus prompt:

    SQL> @ORACLE_BASE\ORACLE_HOME\com\demos\worddem.sql;

    This script creates a Microsoft Word document (worddemo.doc) in the C:\ directory. The document contains data from the EMPLOYEES and JOBS tables. These tables are available in the human resources schema in the sample schema.

  2. Open the worddemo.doc file to see its contents.

Core Functionality

The following subsections describe the APIs that the Microsoft Word demo exposes. These APIs are primitive and do not expose all the functionalities that Microsoft Word exposes through COM Automation.

CreateWordObject

This API instantiates a Word.Basic object in the Microsoft Word Automation server.

Syntax

FUNCTION CreateWordObject() RETURN BINARY_INTEGER;

Remarks

This function must be called before any other operation can be performed. This function returns 0 when successful, or a nonzero value for HRESULT when an error occurs.

FileNew

This API creates a new Microsoft Word document.

Syntax

FUNCTION FileNew() RETURN BINARY_INTEGER;

Remarks

This function returns 0 when successful, or a nonzero value for HRESULT when an error occurs.

FileLoad

This API loads a document into Microsoft Word.

Syntax

FUNCTION FileLoad(filename VARCHAR2) RETURN BINARY_INTEGER;
Where Is
filename the fully qualified filename of the document.

Remarks

This function returns a 0 when successful or a nonzero HRESULT when an error occurs.

FileSave

This API saves the current Microsoft Word document to disk.

Syntax

FUNCTION FileSave() RETURN BINARY_INTEGER;

Remarks

This function returns 0 when successful, or a nonzero value for HRESULT when an error occurs.

FileSaveAs

This API saves the current Microsoft Word document as a specific file.

Syntax

FUNCTION FileSaveAs(filename VARCHAR2) RETURN BINARY_INTEGER;
Where Is
filename the fully qualified filename of the document.

Remarks

This function returns 0 when successful, or a nonzero value for HRESULT when an error occurs.

FileClose

This API closes the current Microsoft Word document.

Syntax

FUNCTION FileClose() RETURN BINARY_INTEGER;

Remarks

This function returns 0 when successful, or a nonzero value for HRESULT when an error occurs.

InsertText

This API inserts a text string into the current Microsoft Word document.

Syntax

FUNCTION InsertText(textstr VARCHAR2) RETURN BINARY_INTEGER;
Where Is
textstr the text that will be inserted into the document.

Remarks

This function returns 0 when successful, or a nonzero value for HRESULT when an error occurs.

InsertNewLine

This API inserts a new line into the current Microsoft Word document.

Syntax

FUNCTION InsertNewLine() RETURN BINARY_INTEGER;

Remarks

This function returns 0 when successful, or a nonzero value for HRESULT when an error occurs.

FormatFontSize

This API sets the font size for the current Microsoft Word document.

Syntax

FUNCTION FormatFontSize(fontsize BINARY_INTEGER) RETURN BINARY_INTEGER;
Where Is
fontsize the point size of the font.

Remarks

This function returns 0 when successful, or a nonzero value for HRESULT when an error occurs.

Microsoft Excel Demo

The following sections detail how to install the Microsoft Excel demo and describe the APIs that it exposes. This demo is provided as an example of the types of solutions that can be built with Oracle and Microsoft Excel.

The Microsoft Excel demo provides the following:

  • ORDExcel, a PL/SQL package that exposes several APIs for manipulating Microsoft Excel. This package is created by the excelsol.sql script.

  • exceldem.sql, a script that displays the capabilities of exchanging data between Oracle database instance and Microsoft Excel. It exchanges data from the EMPLOYEES and JOBS tables in Oracle database instance to a Microsoft Excel spreadsheet and puts it in a graph. These tables are available in the human resources schema in the sample schema.

Installing the Microsoft Excel Demo

Microsoft Excel must be installed on the local computer before you install this demo.

To install the Microsoft Excel demo:

  1. Start SQL*Plus.

    C:\> sqlplus /NOLOG

  2. Connect to the Oracle Database instance as the user who will use the Microsoft Excel demo. For example:

    SQL> connect hr
Enter password: password

  3. Run the excelsol.sql script at the SQL*Plus prompt:

    SQL> @ORACLE_BASE\ORACLE_HOME\com\demos\excelsol.sql;

    This script creates the ORDExcel package in the schema of the current user. You will receive the following error several times when you run this script for the first time:

    ORA-04043: object XXXX does not exist.

    These messages are normal.

Using the Microsoft Excel Demo

To use the Microsoft Excel demo:

  1. Run the exceldem.sql script at the SQL*Plus prompt:

    SQL> @ORACLE_BASE\ORACLE_HOME\com\demos\exceldem.sql;

    This script creates a Microsoft Excel spreadsheet (excelxxxxx.xls) in the C:\ directory. The document contains data from the EMPLOYEES and JOBS tables. These tables are available in the human resources schema in the sample schema.

  2. Open the excelxxxxx.xls file, where xxxxx is a time stamp, to see the content of this file.

Core Functionality

The following subsections describe the APIs that the Microsoft Excel demo exposes. These APIs are primitive and do not expose all the functionalities that Microsoft Excel exposes through COM Automation.

CreateExcelWorkSheet

This API starts the Microsoft Excel COM Automation server and instantiates the objects for a workbook and a worksheet.

Syntax

FUNCTION CreateExcelWorkSheet() RETURN BINARY_INTEGER;

Remarks

This function returns 0 when successful, or a nonzero value for HRESULT when an error occurs.

InsertData

This API inserts any kind of data into a specific cell of the current Excel worksheet.

Syntax

FUNCTION InsertData(range VARCHAR2, data any_PL/SQL_data type, data type VARCHAR2) RETURN BINARY_INTEGER;
Where Is
range a string that indicates a specific cell in the current Excel worksheet (for example, 'A1', 'B1').
data the data that you want to insert into the current Excel worksheet.
data type a string that indicates the data type of the data that you are inserting into Excel. The list of available data types are:
 
  • I2 - 2-byte integer

  • I4 - 4-byte integer

  • R4 - IEEE 4-byte real

  • R8 - IEEE 8-byte real

  • SCODE - error code

  • CY - currency

  • DISPATCH - dispatch pointer

  • BSTR - String

  • BOOL - boolean

  • DATE - date
any_PL/SQL_data type any data type supported by COM Automation Feature.

Remarks

This function returns 0 when successful, or a nonzero value for HRESULT when an error occurs.

InsertChart

This API creates a chart of a specified range of data and inserts the chart at the x and y position of the current worksheet with the desired height and width.

Syntax

FUNCTION InsertChart(xpos BINARY_INTEGER, ypos BINARY_INTEGER, width BINARY_INTEGER, height BINARY_INTEGER,
 range VARCHAR2, type VARCHAR2) RETURN BINARY_INTEGER;
Where Is
xpos the x position in the current worksheet where the chart should be inserted
ypos the y position in the current worksheet where the chart should be inserted
width the width of the chart
height the height of the chart
range the range of cells to be graphed
type the data type of the data to be graphed

Remarks

This function returns 0 when successful, or a nonzero value for HRESULT when an error occurs.

SaveExcelFile

This API saves the current Microsoft Excel workbook as a specific file.

Syntax

FUNCTION SaveExcelFile(filename VARCHAR2) RETURN BINARY_INTEGER;
Where Is
filename the fully qualified file name of the Excel workbook

Remarks

This function returns 0 when successful, or a nonzero value for HRESULT when an error occurs.

ExitExcel

Performs some cleanup and destroys the outstanding references to the Excel COM Automation server. This should be the last API called.

Syntax

FUNCTION ExitExcel() RETURN BINARY_INTEGER;

Remarks

This function returns a 0 when successful or a nonzero HRESULT when an error occurs.

Microsoft PowerPoint Demo

The following sections detail how to install the Microsoft PowerPoint demo and describe the APIs that it exposes. This demo is provided as an example of the types of solutions that can be built with Oracle Database instance and Microsoft PowerPoint.

The Microsoft PowerPoint demo provides the following:

  • ORDPPT, a PL/SQL package that exposes several APIs for manipulating Microsoft PowerPoint. This package is created by the pptsol.sql script.

  • pptdem.sql, a script that displays the capabilities of exchanging data between Oracle Database instance and Microsoft PowerPoint. It exchanges data from the EMPLOYEES and JOBS tables in Oracle Database instance to a Microsoft PowerPoint document. These tables are available in the human resources schema in the sample schema.

Installing the Microsoft PowerPoint Demo

Microsoft PowerPoint must be installed on the local computer before installing this demo.

To install the Microsoft PowerPoint demo:

  1. Start SQL*Plus.

    C:> sqlplus /NOLOG

  2. Connect to the Oracle Database instance as the user who will use the Microsoft PowerPoint demo. For example:

    SQL> connect hr
Enter password: password

  3. Run the pptsol.sql script at the SQL*Plus prompt:

    SQL> @ORACLE_BASE\ORACLE_HOME\com\demos\pptsol.sql;

    This script creates the ORDPPT package in the current user's schema. You will receive the following error several times when you run this script for the first time:

    ORA-04043: object XXXX does not exist.

    These messages are normal.

Using the Microsoft PowerPoint Demo

To run the Microsoft PowerPoint demo:

  1. Run the pptdem.sql script at the SQL*Plus prompt:

    SQL> @ORACLE_BASE\ORACLE_HOME\com\demos\pptdem.sql;

    This script creates a Microsoft PowerPoint presentation (pptdemo.ppt) on C:\. The document contains a list of employee names.

  2. Open pptdemo.ppt to see its contents.

Core Functionality

The following subsections describe the APIs that the Microsoft PowerPoint demo exposes. These APIs are primitive and do not expose all the functionalities that Microsoft PowerPoint exposes through COM Automation.

CreatePresentation

This API starts the Microsoft PowerPoint COM Automation server and instantiates the objects for a presentation.

Syntax

FUNCTION CreatePresentation (servername IN VARCHAR2) RETURN BINARY_INTEGER;
Where Is
servername Microsoft Powerpoint COM Automation Server name

Remarks

This function returns 0 when successful, or a nonzero value for HRESULT when an error occurs.

AddSlide

This API inserts a new slide in the PowerPoint presentation.

Syntax

FUNCTION AddSlide (layout IN BINARY_INTEGER) RETURN BINARY_INTEGER;
Where Is
layout the layout of the new slide

Remarks

This function returns 0 when successful, or a nonzero value for HRESULT when an error occurs.

SetTitle

This API specifies the title of the PowerPoint slide.

Syntax

FUNCTION SetTitle (title IN VARCHAR2) RETURN BINARY_INTEGER;
Where Is
title Powerpoint slide title

Remarks

This function returns 0 when successful, or a nonzero value for HRESULT when an error occurs.

InsertText

This API inserts text into the specified location on the slide.

Syntax

FUNCTION InsertText (orientation IN BINARY_INTEGER, left IN BINARY_INTEGER, top IN BINARY_INTEGER, 
width IN BINARY_INTEGER, height IN BINARY_INTEGER, text IN VARCHAR2) RETURN BINARY_INTEGER;
Where Is
orientation orientation of the text box
left distance between the left edge of the text box and the left edge of the slide in pixels
top distance between the top edge of the text box and the top edge of the slide in pixels
width width of the text box in pixels
height height of the text box in pixels
text text entered in the text box

Remarks

This function returns 0 when successful, or a nonzero value for HRESULT when an error occurs.

PresentationSave

This API saves the current PowerPoint presentation.

Syntax

FUNCTION PresentationSave RETURN BINARY_INTEGER;

Remarks

This function returns 0 when successful, or a nonzero value for HRESULT when an error occurs.

PresentationSaveAs

This API saves the current presentation using the specified name.

Syntax

FUNCTION PresentationSaveAs (filename IN VARCHAR2) RETURN BINARY_INTEGER;
Where Is
filename the fully qualified filename of the presentation.

Remarks

This function returns 0 when successful, or a nonzero value for HRESULT when an error occurs.

PresentationClose

This API closes the current PowerPoint presentation.

Syntax

FUNCTION PresentationClose RETURN BINARY_INTEGER;

Remarks

This function returns 0 when successful, or a nonzero value for HRESULT when an error occurs.

Exit

This API exits the PowerPoint program.

Syntax

FUNCTION Exit RETURN BINARY_INTEGER;

Remarks

This function returns 0 when successful, or a nonzero value for HRESULT when an error occurs.

MAPI Demo

The following sections detail how to install the messaging application programming interface (MAPI) demo and describe the APIs that it exposes. This demo is provided as an example of the types of solutions that can be built with an Oracle Database instance and MAPI-compliant applications.

The MAPI demo provides the following:

  • ORDMAPI, a PL/SQL package that exposes several APIs for manipulating the Extended MAPI client.

  • mapidem.sql, a script that displays the capabilities of exchanging data between Oracle Database instance and the Extended MAPI client.

  • mapi.reg, a registration entry file that updates the registry settings.

Setting Up the Environment to Use the MAPI Demo

You must set up certain related applications to use the MAPI demo.

Note:

The following setup requires Microsoft Outlook 2000 or later. Outlook Express will not work.

To set up the environment for the MAPI demo:

  1. Install Exchange Server and create a new account as follows:

    Select Start, Programs, Microsoft Exchange, and then Active Directory Users and Computers.

    Select your domain and expand the folders. Select users and right-click to create a new user.

  2. Install Microsoft Outlook as follows:

    Select Custom Install. Select Collaboration Data Objects.

    Note:

    During the installation, these are not installed by default.

    Select the Corporate or Workgroup option.

  3. Configure Microsoft Outlook and set connection information as follows:

    Add the account that you created on Exchange Server.

    Enter your incoming and outgoing mail servers, and enter the account name and password.

    Select the connection type (for example, LAN).

  4. Set Microsoft Outlook as the default program for the e-mail, newsgroups, and calendar tools as follows:

    From Internet Explorer, choose Tools, Internet Options, Programs and set the fields.

  5. Patch CDO.DLL as mentioned in the MSDN article, 268272. This patched DLL is part of Microsoft Exchange 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

  6. Update the registry settings by double-clicking MAPI.REG from the Windows Explorer. MAPI.REG is located in:

    ORACLE_BASE\ORACLE_HOME\com\demos

Preparing to Install MAPI Demo

The MAPI Solution invokes Extended MAPI client on behalf of the Oracle Database server. The Oracle Database service on Windows 2000 and higher, by default, runs as the system user LocalSystem. The MAPI profile for user LocalSystem is not easily configured. Before using the MAPI Solution, change both the Oracle Database service and OracleHOME_NAMETNSListenerservice to start up using a login user account.

To prepare to install the MAPI demo:

  1. Log on to Windows using your local user account or domain user account, for example, DOMAIN-1\hr.

  2. Start the MAPI server (for example, Microsoft Outlook) and configure the MAPI profile for the Windows 2000 and higher user DOMAIN-1\hr. Ensure that you can send out e-mail using this profile.

  3. Go to Windows Control Panel/Services.

  4. Shut down the OracleHOME_NAMETNSListener service.

  5. Select the OracleHOME_NAMETNSListener service and click Startup.

  6. Change the Log On As to This Account and fill in DOMAIN-1\hr.

  7. Enter the password and confirm the password for DOMAIN-1\hr.

  8. Restart the OracleHOME_NAMETNSListener service.

  9. Shut down the Oracle Database service.

  10. Select the Oracle Database service and click Startup.

  11. Change Log On As to This Account and fill in DOMAIN-1\hr.

  12. Enter the password and confirm the password for DOMAIN-1\hr.

  13. Restart the Oracle Database service.

Installing the MAPI Demo

The MAPI application, such as Microsoft Outlook 2000 or later, must be installed on the local computer before you install this demo.

To install the MAPI demo:

  1. Start SQL*Plus.

    C:> sqlplus /NOLOG

  2. Connect to the Oracle Database instance as the user who will use the MAPI demo. For example:

    SQL> connect hr
Enter password: password

  3. Run the mapisol.sql script at the SQL*Plus prompt:

    SQL> @ORACLE_BASE\ORACLE_HOME\com\demos\mapisol.sql;

    This script creates the ORDMAPI package in the current user's schema. You will receive the following error several times when you run this script for the first time:

    ORA-04043: object XXXX does not exist.

    These messages are normal.

Using the MAPI Demo

To use the MAPI demo:

  1. Open mapidem.sql 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, MS Exchange Settings. Save the changes.

  2. Run the mapidem.sql script at the SQL*Plus prompt:

    SQL> @ORACLE_BASE\ORACLE_HOME\com\demos\mapidem.sql;

    This script connects to a database server, extracts the data, and sends an e-mail to a specified recipient.

Core Functionality

The following subsections describe the APIs that the MAPI demo exposes. These APIs are primitive and do not expose all the functionalities that MAPI exposes through COM Automation.

CreateMAPISession

This API starts the MAPI COM Automation server and instantiates the objects for a session.

Syntax

FUNCTION CreateMAPISession (servername IN VARCHAR2 DEFAULT '', profilename IN VARCHAR2 DEFAULT NULL, 
password IN VARCHAR2 DEFAULT NULL) RETURN BINARY_INTEGER;
Where Is
servername MAPI server name
profilename name of the profile present in the MAPI server
password password to connect to the MAPI server

Remarks

This function returns 0 when successful, or a nonzero value for HRESULT when an error occurs.

NewMessage

This API creates a new message.

Syntax

FUNCTION NewMessage RETURN BINARY_INTEGER;

Remarks

This function returns 0 when successful, or a nonzero value for HRESULT when an error occurs.

AddRecipient

This API adds the e-mail address of a recipient. This is the address where the e-mail message will be sent.

Syntax

FUNCTION AddRecipient (emailaddress VARCHAR2) RETURN BINARY_INTEGER;
Where Is
emailaddress e-mail address of the recipient

Remarks

This function returns a 0 when successful or a nonzero HRESULT when an error occurs.

SetSubject

This API specifies the subject of the e-mail message.

Syntax

FUNCTION SetSubject (subject VARCHAR2) RETURN BINARY_INTEGER;
Where Is
subject the subject of the e-mail message

Remarks

This function returns 0 when successful, or a nonzero value for HRESULT when an error occurs.

SetBody

This API inserts the body text of the e-mail message.

Syntax

FUNCTION SetBody (messagetext VARCHAR2) RETURN BINARY_INTEGER;
Where Is
messagetext the body of the e-mail message

Remarks

This function returns 0 when successful, or a nonzero value for HRESULT when an error occurs.

SendMessage

This API sends the e-mail message to the specified recipients.

Syntax

FUNCTION SendMessage RETURN BINARY_INTEGER;

Remarks

This function returns 0 when successful, or a nonzero value for HRESULT when an error occurs.

EndMAPISession

This API exits the MAPI session.

Syntax

FUNCTION EndMAPISession RETURN BINARY_INTEGER;

Remarks

This function returns 0 when successful, or a nonzero value for HRESULT when an error occurs.