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 PL/SQL Demos
Microsoft Word Demo
Microsoft Excel Demo
Microsoft PowerPoint Demo
MAPI Demo

Overview of 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 from Oracle to Word
Microsoft Excel Demo - Exchanges data from Oracle to Excel
Microsoft PowerPoint Demo - Exchanges data from Oracle to PowerPoint
MAPI Demo - Exchanges data from Oracle to Messaging Application Programming Interface (MAPI) compliant applications

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 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 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 installing this demo.

To install the Microsoft Word demos:

Start SQL*Plus.
```
C:\> sqlplus /NOLOG
```
Connect to the Oracle database instance as the user that will use the Microsoft Word demo. For example:
```
SQL> connect hr/hr
```
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 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:

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.
Open worddemo.doc to see its contents.

Core Functionality

The following subsections describe the APIs that the Microsoft Word demo exposes. These APIs are primitive. Be aware that much of the functionality that Microsoft Word exposes through COM Automation is not exposed through these APIs.

CreateWordObject

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 procedure returns a 0 when successful or a nonzero HRESULT when an error occurs.

FileNew

Creates a new Microsoft Word document.

Syntax

FUNCTION FileNew() RETURN BINARY_INTEGER;

Remarks

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

FileLoad

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 procedure returns a 0 when successful or a nonzero HRESULT when an error occurs.

FileSave

Saves the current Microsoft Word document to disk.

Syntax

FUNCTION FileSave() RETURN BINARY_INTEGER;

Remarks

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

FileSaveAs

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 procedure returns a 0 when successful or a nonzero HRESULT when an error occurs.

FileClose

Closes the current Microsoft Word document.

Syntax

FUNCTION FileClose() RETURN BINARY_INTEGER;

Remarks

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

InsertText

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 procedure returns a 0 when successful or a nonzero HRESULT when an error occurs.

InsertNewLine

Inserts a carriage return into the current Microsoft Word document.

Syntax

FUNCTION InsertNewLine() RETURN BINARY_INTEGER;

Remarks

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

FormatFontSize

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 procedure returns a 0 when successful or a nonzero 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 and Microsoft Excel. It exchanges data from the EMPLOYEES and JOBS tables in Oracle 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 installing this demo.

To install the Microsoft Excel demo:

Start SQL*Plus.
```
C:\> sqlplus /NOLOG
```
Connect to the Oracle database instance as the user that will use the Microsoft Excel demo. For example:
```
SQL> connect hr/hr
```
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 current user's schema. You 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:

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.
Open the excelxxxxx.xls file, where xxxxx is a time stamp, to see its contents.

Core Functionality

The following subsections describe the APIs that the Microsoft Excel demo exposes. These APIs are primitive. Be aware that much of the functionality that Microsoft Excel exposes through COM Automation is not exposed through these APIs.

CreateExcelWorkSheet

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 procedure returns a 0 when successful or a nonzero HRESULT when an error occurs.

InsertData

Inserts any kind of data into a specific cell of the current Excel worksheet.

Syntax

FUNCTION InsertData(range VARCHAR2, data any_PL/SQL_datatype, datatype 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.
`datatype`	a string that indicates the datatype of the data that you are inserting into Excel. The list of available datatypes 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_datatype`	any datatype supported by COM Automation Feature.

Remarks

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

InsertChart

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 datatype of the data to be graphed.

Remarks

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

SaveExcelFile

Saves the current Microsoft Excel workbook as a specific file.

Syntax

FUNCTION SaveExcelFile(filename VARCHAR2) RETURN BINARY_INTEGER;

Where	Is
`filename`	the fully qualified `filename` of the Excel workbook

Remarks

This procedure returns a 0 when successful or a nonzero 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 procedure 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 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 and Microsoft PowerPoint. It exchanges data from the EMPLOYEES and JOBS tables in Oracle 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:

Start SQL*Plus.
```
C:> sqlplus /NOLOG
```
Connect to the Oracle database instance as the user that will use the Microsoft PowerPoint demo. For example:
```
SQL> connect hr/hr
```
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 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:

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.
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. Be aware that much of the functionality that Microsoft PowerPoint exposes through COM Automation is not exposed through these APIs.

CreatePresentation

Starts the Microsoft PowerPoint COM Automation server and instantiates the objects for a presentation.

Syntax

FUNCTION CreatePresentation (servername IN VARCHAR2) RETURN BINARY_INTEGER;

Remarks

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

AddSlide

Inserts a new slide in the PowerPoint presentation.

Syntax

FUNCTION AddSlide (layout IN BINARY_INTEGER) RETURN BINARY_INTEGER;

Remarks

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

SetTitle

Specifies the title of the PowerPoint slide.

Syntax

FUNCTION SetTitle (title IN VARCHAR2) RETURN BINARY_INTEGER;

Remarks

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

InsertText

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;

Remarks

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

PresentationSave

Saves the current PowerPoint presentation.

Syntax

FUNCTION PresentationSave RETURN BINARY_INTEGER;

Remarks

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

PresentationSaveAs

Saves the current presentation using the specified name.

Syntax

FUNCTION PresentationSaveAs (filename IN VARCHAR2) RETURN BINARY_INTEGER;

Remarks

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

PresentationClose

Closes the current PowerPoint presentation.

Syntax

FUNCTION PresentationClose RETURN binary_integer;

Remarks

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

Exit

Exits the PowerPoint program.

Syntax

FUNCTION Exit RETURN BINARY_INTEGER;

Remarks

This procedure returns a 0 when successful or a nonzero 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 Oracle and MAPI-compliant applications.

The MAPI demo provides the following:

ORDMAPI, a PL/SQL package that exposes several APIs for manipulating Extended MAPI client.
mapidem.sql, a script that displays the capabilities of exchanging data between Oracle and 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 in order 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:

Install Exchange Server and create a new account as follows:

Start > Programs > Microsoft Exchange > Active Directory Users and Computers.

Select your domain and expand the folders. Select users, then right-click to create a new user.
Install Microsoft Outlook as follows:

Select Custom Install. Select the Collaboration Data Objects (these are not installed by default).

During the install, select the Corporate or Workgroup option.
Configure Microsoft Outlook and set connection information as follows:

Add the account 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).
Set Microsoft Outlook as the default program for the e-mail, newsgroups, and calender tools as follows:

From Internet Explorer, choose Tools > Internet Options > Programs and set the fields.
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
```
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 the behalf of the Oracle database server. The Oracle database service on Windows NT, by default, runs as NT system user LocalSystem. The MAPI profile for user LocalSystem is not easily configured. Before using the MAPI Solution, change both the Windows NT Oracle database service and OracleHOME_NAMETNSListener service to start up using a Windows NT login user account.

To prepare to install the MAPI demo:

Log on to Windows NT using your local user account or domain user account, for example, DOMAIN-1\hr.
Start the MAPI server (for example, Microsoft Outlook) and configure the MAPI profile for the Windows NT user DOMAIN-1\hr. Make sure that you are able to send out e-mail using this profile.
Go to the Windows NT Control Panel/Services.
Shut down the OracleHOME_NAMETNSListener service.
Select the OracleHOME_NAMETNSListener service and click Startup.
Change the Log On As to This Account and fill in DOMAIN-1\hr.
Enter the password and confirm the password for DOMAIN-1\hr.
Restart the OracleHOME_NAMETNSListener service.
Shut down the Oracle database service.
Select the Oracle database service and click Startup.
Change the Log On As to This Account and fill in DOMAIN-1\hr.
Enter the password and confirm the password for DOMAIN-1\hr.
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 installing this demo.

To install the MAPI demo:

Start SQL*Plus.
```
C:> sqlplus /NOLOG
```
Connect to the Oracle database instance as the user that will use the MAPI demo. For example:
```
SQL> connect hr/hr
```
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 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:

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 in that is indicated in ORDMapi.CreateMAPISession, MS Exchange Settings. Save the changes.
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. Be aware that much of the functionality that MAPI exposes through COM Automation is not exposed through these APIs.

CreateMAPISession

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;

Remarks

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

NewMessage

Creates a new message.

Syntax

FUNCTION NewMessage RETURN BINARY_INTEGER;

Remarks

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

AddRecipient

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;

Remarks

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

SetSubject

Specifies the subject of the e-mail message.

Syntax

FUNCTION SetSubject (subject VARCHAR2) RETURN BINARY_INTEGER;

Remarks

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

SetBody

Inserts the body text of the e-mail message.

Syntax

FUNCTION SetBody (messagetext VARCHAR2) RETURN BINARY_INTEGER;

Remarks

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

SendMessage

Sends the e-mail message to the specified recipients.

Syntax

FUNCTION SendMessage RETURN BINARY_INTEGER;

Remarks

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

EndMAPISession

Exits the MAPI session.

Syntax

FUNCTION EndMAPISession RETURN BINARY_INTEGER;

Remarks

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