5
Using Oracle COM Automation Demos

This chapter describes how to use Oracle COM Automation feature demonstration programs. Specific topics discussed are:

• Oracle COM Automation Feature Demos Overview

• Microsoft Word Demo

• Microsoft Excel Demo

• Microsoft PowerPoint Demo

• MAPI Demo

Oracle COM Automation Feature Demos Overview

Oracle COM Automation feature includes demos to give you an idea of how to use Oracle COM Automation feature to build solutions. These demos provide base functionality. They are provided as examples of how to use Oracle COM Automation feature and as a foundation upon which to build more customized, complex applications that use OLE Automation.

Each demo exposes a core set of APIs that enables you to do simple operations using OLE Automation. Each OLE 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, Oracle Corporation 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 Microsoft Word demo provides a PL/SQL package (ORDWord) that exposes several APIs for manipulating Microsoft Word. Also, the Microsoft Word demo includes a script to demonstrate the capabilities of exchanging data between Oracle and Microsoft Word. The worddem.sql script exchanges data from the EMP table in Oracle to a Microsoft Word document.

Installing the Microsoft Word Demo

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

To install the Microsoft Word demo, perform the following steps:

1. Change to the following directory from the MS-DOS command prompt:

   C:\> cd oracle_base\oracle_home\com\demos
   
   

2. Start Server Manager:

   C:\> svrmgrl
   
   

3. Connect to the Oracle database instance as the user that will use the Microsoft Word demo. For example, SCOTT/TIGER.

   SVRMGR> CONNECT SCOTT/TIGER
   
   

4. Run the wordsol.sql script at the Server Manager prompt:

   SVRMGR> @ORACLE_BASE\ORACLE_HOME\COM\DEMOS\WORDSOL.SQL
   
   

   This script creates 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 Demo

To run the Microsoft Word demo, perform the following steps:

1. Change to the following directory from the MS-DOS command prompt:

   C:\> cd oracle_base\oracle_home\com\demos
   
   

2. Start Server Manager:

   C:\> svrmgrl
   
   

3. Connect to the Oracle database instance as the user that will use the Microsoft Word demo. For example, SCOTT/TIGER.

   SVRMGR> CONNECT SCOTT/TIGER
   
   

4. Run the worddem.sql script at the Server Manager prompt:

   SVRMGR> @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 EMP table.

5. 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 OLE Automation is not exposed through these APIs. These APIs and PL/SQL code are provided as a proof of concept that Oracle COM Automation feature is a viable development feature.

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 non-zero 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 non-zero HRESULT when an error occurs.

FileLoad

Loads a document into Microsoft Word.

Syntax

FUNCTION FileLoad(filename VARCHAR2) RETURN BINARY_INTEGER;

where:

filename

is the fully qualified file name of the document.

Remarks

This procedure returns a 0 when successful or a non-zero HRESULT when an error occurs.

FileSave

Saves the current active Microsoft Word document to disk.

Syntax

FUNCTION FileSave() RETURN BINARY_INTEGER;

Remarks

This procedure returns a 0 when successful or a non-zero HRESULT when an error occurs.

FileSaveAs

Saves the current active Microsoft Word document as a specific file.

Syntax

FUNCTION FileSaveAs(filename VARCHAR2) RETURN BINARY_INTEGER;

where:

filename

is the fully qualified file name of the document.

Remarks

This procedure returns a 0 when successful or a non-zero HRESULT when an error occurs.

FileClose

Closes the current active Microsoft Word document.

Syntax

FUNCTION FileClose() RETURN BINARY_INTEGER;

Remarks

This procedure returns a 0 when successful or a non-zero HRESULT when an error occurs.

InsertText

Inserts a text string into the current active Microsoft Word document.

Syntax

FUNCTION InsertText(textstr VARCHAR2) RETURN BINARY_INTEGER;

where:

textstr

is the text that will be inserted into the document.

Remarks

This procedure returns a 0 when successful or a non-zero HRESULT when an error occurs.

InsertNewLine

Inserts a carriage return into the current active Microsoft Word document.

Syntax

FUNCTION InsertNewLine() RETURN BINARY_INTEGER;

Remarks

This procedure returns a 0 when successful or a non-zero HRESULT when an error occurs.

FormatFontSize

Sets the font size for the current active Microsoft Word document.

Syntax

FUNCTION FormatFontSize(fontsize BINARY_INTEGER) RETURN BINARY_INTEGER;

where:

fontsize

is the point of the font.

Remarks

This procedure returns a 0 when successful or a non-zero 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 a PL/SQL package (ORDExcel) that exposes several APIs for manipulating Microsoft Excel. Also, the Microsoft Excel demo includes a script to demonstrate the capabilities of exchanging data between Oracle and Microsoft Excel. The exceldem.sql script exchanges data from the EMP table in Oracle to a Microsoft Excel worksheet and puts it in a graph. Run this script after installing the demo.

Installing the Microsoft Excel Demo

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

To install the Microsoft Excel demo, perform the following steps:

1. Change to the following directory from the MS-DOS command prompt:

   C:\> cd oracle_base\oracle_home\com\demos
   
   

2. Start Server Manager:

   C:\> svrmgrl
   
   

3. Connect to the Oracle database instance as the user that will use the Microsoft Excel demo. For example, SCOTT/TIGER.

   SVRMGR> CONNECT SCOTT/TIGER
   
   

4. Run the excelsol.sql script at the Server Manager prompt:

   SVRMGR> @ORACLE_BASE\ORACLE_HOME\COM\DEMOS\EXCELSOL.SQL
   
   

   This script creates the ORDExcel package in the current user's schema. You will receive several ORA-04043: object XXXX does not exist messages when you run this script for the first time. These messages are normal.

Using the Microsoft Excel Demo

To run the Microsoft Excel demo, perform the following steps:

1. Change to the following directory from the MS-DOS command prompt:

   C:\> cd oracle_base\oracle_home\com\demos
   
   

2. Start Server Manager:

   C:\> svrmgrl
   
   

3. Connect to the Oracle database instance as the user that will use the Microsoft Excel demo. For example, SCOTT/TIGER.

   SVRMGR> CONNECT SCOTT/TIGER
   
   

4. Run the exceldem.sql script at the Server Manager prompt:

   SVRMGR> @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 EMP table.

5. Open the excelxxxxx.xls file, where xxxxx is a timestamp, 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 OLE Automation is not exposed through these APIs. These APIs and PL/SQL code are provided as a "proof of concept" that Oracle COM Automation feature is viable.

CreateExcelWorkSheet

Starts the Microsoft Excel OLE 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 non-zero HRESULT when an error occurs.

InsertData

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

Syntax

FUNCTION InsertData(range VARCHAR2, data ANY PL/SQL DATATYPE, datatype VARCHAR2) 
RETURN BINARY_INTEGER;

where:

range	a string that indicates a specific cell in the current Excel worksheet (for example, `A1', `B1').
data	is 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

Remarks

This procedure returns a 0 when successful or a non-zero 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:

xpos	is the x position in the current worksheet where the chart should be inserted.
ypos	is the y position in the current worksheet where the chart should be inserted.
width	is the width of the chart.
height	is the height of the chart.
range	is the range of cells to be graphed.
type	is the datatype of the data to be graphed.

Remarks

This procedure returns a 0 when successful or a non-zero HRESULT when an error occurs.

SaveExcelFile

Saves the current active Microsoft Excel workbook as a specific file.

Syntax

FUNCTION SaveExcelFile(filename VARCHAR2) RETURN BINARY_INTEGER;

where:

filename

is the fully qualified file name of the Excel workbook.

Remarks

This procedure returns a 0 when successful or a non-zero HRESULT when an error occurs.

ExitExcel

Performs some cleanup and destroys the outstanding references to the Excel OLE 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 non-zero 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 a PL/SQL package (ORDPPT) that exposes several APIs for manipulating Microsoft PowerPoint. Also, the Microsoft PowerPoint demo includes a script (pptdem.sql) to demonstrate the capabilities of exchanging data between Oracle and Microsoft PowerPoint. Run this script after installing the demo.

Installing the Microsoft PowerPoint Demo

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

To install the Microsoft PowerPoint demo, perform the following steps:

1. Change to the following directory from the MS-DOS command prompt:

   C:\> cd oracle_base\oracle_home\com\demos
   
   

2. Start Server Manager:

   C:\> svrmgrl
   
   

3. Connect to the Oracle database instance as the user that will use the Microsoft PowerPoint demo. For example, SCOTT/TIGER.

   SVRMGR> CONNECT SCOTT/TIGER
   
   

4. Run the pptsol.sql script at the Server Manager prompt:

   SVRMGR> @ORACLE_BASE\ORACLE_HOME\COM\DEMOS\PPTSOL.SQL
   
   

   This script creates the ORDPPT package in the current user's schema. You will receive several ORA-04043: object XXXX does not exist messages when you run this script for the first time. These messages are normal.

Using the Microsoft PowerPoint Demo

To run the Microsoft PowerPoint demo, perform the following steps:

1. Change to the following directory from the MS-DOS command prompt:

   C:\> cd oracle_base\oracle_home\com\demos
   
   

2. Start Server Manager:

   C:\> svrmgrl
   
   

3. Connect to the Oracle database instance as the user that will use the Microsoft PowerPoint demo. For example, SCOTT/TIGER.

   SVRMGR> CONNECT SCOTT/TIGER
   
   

4. Run the pptdem.sql script at the Server Manager prompt:

   SVRMGR> @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 table names owned by the user account, such as SCOTT.

5. 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 OLE Automation is not exposed through these APIs. These APIs and PL/SQL code are provided as a "proof of concept" that Oracle COM Automation feature is viable.

CreatePresentation

Starts the Microsoft PowerPoint OLE 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 non-zero 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 non-zero 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 non-zero 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 non-zero HRESULT when an error occurs.

PresentationSave

Save the current PowerPoint presentation.

Syntax

FUNCTION PresentationSave RETURN binary_integer;

Remarks

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

MAPI Demo

The following sections detail how to install the 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 Messaging Application Programming Interface (MAPI) compliant applications.

The MAPI demo provides a PL/SQL package (ORDMAPI) that exposes several APIs for manipulating MAPI. Also, the MAPI demo includes a script (mapidem.sql) to demonstrate the capabilities of exchanging data between Oracle and MAPI. Run this script after installing the demo.

Installing the MAPI Demo

The MAPI application, such as MS Outlook 98 or Oracle InterOffice, must be installed on the local computer before installing this demo.

To install the MAPI demo, perform the following steps:

1. Change to the following directory from the MS-DOS command prompt:

   C:\> cd oracle_base\oracle_home\com\demos
   
   

2. Start Server Manager:

   C:\> svrmgrl
   
   

3. Connect to the Oracle database instance as the user that will use the MAPI demo. For example, SCOTT/TIGER.

   SVRMGR> CONNECT SCOTT/TIGER
   
   

4. Run the mapisol.sql script at the Server Manager prompt:

   SVRMGR> @ORACLE_BASE\ORACLE_HOME\COM\DEMOS\MAPISOL.SQL
   
   

   This script creates the ORDMAPI package in the current user's schema. You will receive several ORA-04043: object XXXX does not exist messages when you run this script for the first time. These messages are normal.

Using the MAPI Demo

To run the MAPI demo, perform the following steps:

1. Change to the following directory from the MS-DOS command prompt:

   C:\> cd oracle_base\oracle_home\com\demos
   
   

2. Start Server Manager:

   C:\> svrmgrl
   
   

3. Connect to the Oracle database instance as the user that will use the MAPI demo. For example, SCOTT/TIGER.

   SVRMGR> CONNECT SCOTT/TIGER
   
   

4. Run the mapidem.sql script at the Server Manager prompt:

   SVRMGR> @ORACLE_BASE\ORACLE_HOME\COM\DEMOS\MAPIDEM.SQL
   
   

   This script connects to an Oracle database, extracts the data, and sends an email 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 OLE Automation is not exposed through these APIs. These APIs and PL/SQL code are provided as a "proof of concept" that Oracle COM Automation feature is viable.

CreateMAPISession

Starts the MAPI OLE 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 non-zero 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 non-zero HRESULT when an error occurs.

AddRecipient

Adds the email address of a recipient. These are addresses where the email message will be sent.

Syntax

FUNCTION AddRecipient (emailaddress varchar2) RETURN binary_integer;

Remarks

This procedure returns a 0 when successful or a non-zero HRESULT when an error occurs.

SetSubject

Specifies the subject of the email message.

Syntax

FUNCTION SetSubject (subject varchar2) RETURN binary_integer;

Remarks

This procedure returns a 0 when successful or a non-zero HRESULT when an error occurs.

SetBody

Inserts the body text of the email message.

Syntax

FUNCTION SetBody (messagetext varchar2) RETURN binary_integer;

Remarks

This procedure returns a 0 when successful or a non-zero HRESULT when an error occurs.

SendMessage

Sends the email message to the specified recipients.

Syntax

FUNCTION SendMessage RETURN binary_integer;

Remarks

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