This chapter describes how to use Oracle COM Automation Feature demonstration programs for PL/SQL.
This chapter contains these topics:
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 to Word
Microsoft Excel Demo - Exchanges data to Excel
Microsoft PowerPoint Demo - Exchanges data to PowerPoint
MAPI Demo - Exchanges data from Oracle to Messaging Application Programming Interface (MAPI) compliant applications
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.
Microsoft Word must be installed on the local computer before installing this demo.
To install Microsoft Word demos:
Start SQL*Plus.
C:\> sqlplus /NOLOG
Connect to the Oracle database instance as the user who 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.
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.
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
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 procedure 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 procedure 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 procedure 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 procedure 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 procedure 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 procedure 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 procedure returns 0
when successful, or a nonzero value for HRESULT
when an error occurs.
InsertNewLine
This API inserts a carriage return into the current Microsoft Word document.
Syntax
FUNCTION InsertNewLine() RETURN BINARY_INTEGER;
Remarks
This procedure 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 procedure returns 0
when successful, or a nonzero value for HRESULT
when an error occurs.
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.
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 who 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 schema of the current user. 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.
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 (excel
xxxxx
.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 excel
xxxxx
.xls
file, where xxxxx
is a time stamp, to see the content of this file.
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 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_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: |
|
|
any_PL/SQL_datatype |
any datatype supported by COM Automation Feature. |
Remarks
This procedure 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 datatype of the data to be graphed |
Remarks
This procedure 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 filename of the Excel workbook |
Remarks
This procedure 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 procedure returns a 0
when successful or a nonzero HRESULT
when an error occurs.
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.
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 who 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.
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.
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
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;
Remarks
This procedure 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;
Remarks
This procedure 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;
Remarks
This procedure 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;
Remarks
This procedure 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 procedure 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;
Remarks
This procedure 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 procedure 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 procedure returns 0
when successful, or a nonzero value for HRESULT
when an error occurs.
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 Database instance 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.
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:
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.
Install Microsoft Outlook as follows:
Select Custom Install. Select Collaboration Data Objects. These are not installed by default).
During the installation, 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
The MAPI Solution invokes Extended MAPI client on 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 Oracle
HOME_NAME
TNSListener
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 Oracle
HOME_NAMETNS
Listener
service.
Select the Oracle
HOME_NAMETNS
Listener
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
HOME_NAMETNS
Listener
service.
Shut down the Oracle Database service.
Select the Oracle Database service and click Startup.
Change 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.
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 who 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.
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.
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
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;
Remarks
This procedure 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 procedure 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;
Remarks
This procedure 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;
Remarks
This procedure 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;
Remarks
This procedure 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 procedure 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 procedure returns 0
when successful, or a nonzero value for HRESULT
when an error occurs.