5
Using Oracle COM Automation Feature Demos

This chapter describes how to use Oracle COM Automation feature demos.

Specific topics discussed are:

• Oracle COM Automation Feature Demos Overview
• Microsoft Word Demo
• Microsoft Excel 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 (for example, 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 - Exchange data from Oracle to Word.
• Microsoft Excel Demo - Exchange data from Oracle to Excel.

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 for this demo to work properly.

To install the Microsoft Word demo:

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:

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
 
5. This script creates a Microsoft Word document (WORDDEMO.DOC) in the C:\ directory. The document contains data from the EMP table.

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 for this demo to work properly.

To install the Microsoft Excel demo:

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:

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) on C:\. The document contains data from the EMP table.
 
5. Open the EXCELXXXXX.XLS, 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.