4
Oracle COM Automation Feature Core Functionality

This chapter describes the core functionality of Oracle COM Automation feature.

Specific topics discussed are:

Oracle COM Automation Feature Core Functionality
Developing Solutions Using Oracle COM Automation Feature
PL/SQL Application Programming Interfaces
OLE Automation Errors

Oracle COM Automation Feature Core Functionality

Oracle COM Automation feature provides a mechanism to manipulate COM objects. It acts as a generic wrapper interface of the IDispatch interface (OLE Automation).

The feature externalizes all the methods supported by the IDispatch interface.
COM objects expose properties, data attributes, and methods, functions that perform an action, to the developer.
The IDispatch interface supports three basic operations for any COM object:

Get the value of an exposed property.
Set the value of an exposed property.
Invoke a method on an object.

When an Oracle COM Automation feature API is invoked from PL/SQL, the feature converts the parameters to the appropriate OLE Automation data types and then invokes the corresponding IDispatch API with the converted parameters. The feature externalizes the following APIs:

CreateObject
DestroyObject
GetLastError
GetProperty
SetProperty
InitArg
SetArg
SetPtrArg
Invoke

This section describes each of the APIs and how to use them in PL/SQL blocks. A typical PL/SQL block performs the following steps to create and manipulate a COM object using Oracle COM Automation feature:

Call CreateObject to create the COM object.

Manipulate the COM object using the following APIs:

Call GetProperty to get a property value.
Call SetProperty to set a property value to a new value.

Call Invoke to call a method. As part of preparation for the Invoke API call, you use InitArg, SetArg, and SetPtrArg to package the argument to be sent to the OLE Automation method.

Call GetLastError to get the most recent error information.

Call DestroyObject to destroy the object.

Developing Solutions Using Oracle COM Automation Feature

Oracle COM Automation feature enables you to use components that provide additional functionality that PL/SQL does not support. As mentioned before, you can choose to build your own custom components or use the thousands of pre-built components that are available from third-party ISVs. However, before you can begin building a solution using Oracle COM Automation feature, you must know two things about the COM objects that you intend to use.

You must determine the Program ID of the COM object. The Program ID, or progID, is a descriptive string that maps to the Globally Unique Identifier (GUID), which is a hexadecimal number that uniquely identifies a COM object. An example of a progID is the following string:

Excel.Worksheet.1

Use the progID to tell the CreateObject API which COM object to instantiate.

You must be aware of the types of properties and methods that are exposed through the COM object's IDispatch interface. Usually, the ISV provides documentation describing the names and datatype of the object's properties and the prototypes of the object's methods. Properties are referred to by a descriptive string, such as "xpos" or "ypos". A property can be any standard OLE Automation datatype, such as integer or string. The GetProperty and SetProperty APIs take the property name and a variable of the appropriate datatype. Methods are referred to by a descriptive string, such as "InsertChart". A method takes a set of parameters that are of different OLE Automation datatypes and returns an OLE Automation datatype.

The following is an example of an OLE Automation method prototype in Interface Definition Language (IDL) grammar:

[id(0x6003000)]
           long Post(  [in, out] long* lngAccountNo,
                        [in, out] long* lngAmount,
                        [in, out] BSTR* strResult);

Microsoft provides a tool called the OLE/COM Object Viewer with Microsoft Visual C++ for browsing the properties and methods of COM objects on a local system. This tool enables you to quickly and easily determine the properties and methods that each COM object exposes.

Datatype Conversion

Because Oracle uses PL/SQL datatypes and OLE Automation uses Microsoft Visual Basic datatypes, Oracle COM Automation feature must convert the data that it receives from PL/SQL and pass it to the OLE Automation object, and vice versa.

This table shows the conversion from PL/SQL datatypes to Microsoft Visual Basic datatypes:

PL/SQL Datatype Microsoft Visual Basic Datatype

Varchar2

String

Boolean

Boolean

Binary_Integer

Byte, Integer, or Long

Double Precision

Double, Single, or Currency

Date

Date

PL/SQL Datatype	Microsoft Visual Basic Datatype
Varchar2	String
Boolean	Boolean
Binary_Integer	Byte, Integer, or Long
Double Precision	Double, Single, or Currency
Date	Date

This table shows the conversion from Microsoft Visual Basic datatypes to PL/SQL datatypes:

Microsoft Visual Basic Datatype PL/SQL Datatype

Boolean

Boolean

Long, Integer, Byte, Object

Binary_Integer

String

VarChar2

Double, Single, or Currency

Double Precision

Date

Date

Microsoft Visual Basic Datatype	PL/SQL Datatype
Boolean	Boolean
Long, Integer, Byte, Object	Binary_Integer
String	VarChar2
Double, Single, or Currency	Double Precision
Date	Date

HRESULT Return Codes

These APIs return an integer return code. The return code is 0 when successful or a non-zero HRESULT when an error occurs. An HRESULT is an OLE error code of the hexadecimal form 0x800nnnnn, but when it is returned as a binary_integer value, it has the form -214nnnnnnn.

For example, passing an invalid object name to CreateObject causes it to return a binary_integer HRESULT of -2147221005, which is 0x800401f3 in hexadecimal.

The sections on GetLastError and OLE Automation errors provide additional information on how to interpret the return codes from Oracle COM Automation feature. For complete information on HRESULTs, refer to the Microsoft documentation on HRESULTs.

PL/SQL Application Programming Interfaces

The following section describes the PL/SQL APIs for manipulating COM objects using the OLE Automation interface. Each of the following PL/SQL stored procedures reside in the package ORDCOM.

CreateObject

Instantiates a COM object in an OLE Automation server.

Syntax

FUNCTION CreateObject(progid VARCHAR2, reserved BINARY_INTEGER, servername 
VARCHAR2, objecttoken OUT BINARY_INTEGER) RETURN BINARY_INTEGER;

where:

progid

is the programmatic identifier (progID) of the OLE Automation object to create. This character string describes the class of the OLE Automation object and has the following form:
OLEComponent.Object
OLEComponent is the component name of the OLE Automation server, and Object is the name of the OLE Automation object. The specified OLE Automation object must be creatable and must support the IDispatch interface.

reserved

Currently, this parameter is reserved for future use. Pass a value of 0. Future versions of Oracle COM Automation feature may use this parameter.

servername

is the name of the remote DCOM server on which to instantiate the COM object. Passing a specified name forces Oracle COM Automation feature to attempt to instantiate the COM object on a remote computer. Passing an empty string, for example, `', forces Oracle COM Automation feature to check the registry for the location of the COM object. The registry contains information as to whether the COM object is local or remote. Therefore, to create a local COM object, always pass an empty string and ensure the registry indicates that the COM object exists locally. The registry information for COM objects can be configured with the tool DCOMCNFG.EXE.

objecttoken

is the returned object token. It must be a local variable of datatype binary_integer. This object token identifies the created OLE Automation object and is used in calls to the other Oracle COM Automation feature APIs.

Remarks

The created OLE Automation object must be freed with a corresponding call to DestroyObject. This nullifies the internal representation of the object in the Oracle COM Automation Feature and releases all the interfaces associated with the object. If you do not free the object, the object stays resident until the user's session terminates.

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

Code Sample

hresult binary_integer;
applicationToken binary_integer:=-1;

hresult :=ORDCOM.CreateObject('Excel.Application', 0, `', applicationToken);
IF hresult = -1 THEN
  dbms_output.put_line(hresult);
END IF;

DestroyObject

Destroys a created OLE Automation object.

Syntax

FUNCTION DestroyObject(objecttoken BINARY_INTEGER) RETURN BINARY_INTEGER;

where:

objecttoken

is the object token of an OLE Automation object previously created by CreateObject.

Remarks

If DestroyObject is not called, the created OLE Automation object stays resident until the user's session is terminated. Calling DestroyObject nullifies the internal representation of the object in the Oracle COM Automation Feature and releases all the interfaces associated with the object.

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

Code Sample

hresult binary_integer;
applicationToken binary_integer:=-1;

/*
 At some point before this, we called CreateObject and
 got a valid applicationToken.
*/
hresult:=ORDCOM.DestroyObject(applicationToken);

GetLastError

Obtains the OLE Automation error information about the last error that occurred.

Syntax

FUNCTION GetLastError(source OUT VARCHAR2, description OUT VARCHAR2, helpfile 
OUT VARCHAR2, helpid OUT VARCHAR2) RETURN BINARY_INTEGER;

where:

source

is the source of the error information. If specified, it must be a local char or varchar variable. The return value is truncated to fit the local variable if necessary.

description

is the description of the error. If specified, it must be a local char or varchar variable. The return value is truncated to fit the local variable if necessary.

helpfile

is the Help file for the OLE Automation object. If specified, it must be a local char or varchar variable. The return value is truncated to fit the local variable if necessary.

helpid

is the Help file context ID. If specified, it must be a local int variable.

Remarks

Each call to an Oracle COM Automation feature API (except GetLastError) resets the error information, so that GetLastError obtains error information only for the most recent Oracle COM Automation feature API call. Because GetLastError does not reset the last error information, it can be called multiple times to get the same error information.

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

See "OLE Automation Errors" for a description of the types of errors that can be returned by this function.

Code Sample

applicationToken binary_integer:=-1;
hresult binary_integer;
error_src varchar2(255);
error_description varchar2(255);
error_helpfile varchar2(255);
error_helpID binary_integer;

hresult:=ORDCOM.CreateObject('Excel.Application', 0, `', applicationToken);
IF hresult=-1 THEN
  ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_
helpID);
  dbms_output.put_line(error_src);
  dbms_output.put_line(error_description);
  dbms_output.put_line(error_helpfile);
  return hresult;
END IF;

GetProperty

Gets a property value of an OLE Automation object.

Syntax

FUNCTION GetProperty(objecttoken BINARY_INTEGER, propertyname VARCHAR2, argcount 
BINARY_INTEGER, propertyvalue OUT any PL/SQL datatype) RETURN BINARY_INTEGER;

where:

objecttoken

is the object token of an OLE object previously created by CreateObject.

propertyname

is the property name of the OLE object to return.

argcount

is the index of the property array. If the property is not an array, then the developer should specify 0.

propertyvalue

is the returned property value. The returned property type depends on the OLE Automation type that is returned. You must pass the PL/SQL datatype that corresponds to the Microsoft Visual Basic datatype of the OLE Automation property. Otherwise, the OLE Automation feature will not properly convert the Microsoft Visual Basic datatype.

any PL/SQL datatype

supported by COM Automation Feature.

Remarks

If the property returns an OLE object, you must specify a local variable of datatype binary_integer or the propertyvalue parameter. An object token is stored in the local variable, and this object token can be used with other OLE Automation stored procedures.

When the property returns an array, if propertyvalue is specified, it is set to NULL.

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

Code Sample

ChartObject  binary_integer:=-1;
CharToken    binary_integer := -1
hresult           binary_integer;
/* Previously, ChartObject, CharToken were initialized calling CreateObject */
hresult:=ORDCOM.getProperty(ChartObject, 'Chart', 0, ChartToken);
IF hresult=-1 THEN
  /* Do error checking here */
  return hresult;
END IF;

SetProperty

Sets a property of an OLE Automation object to a new value.

Syntax

FUNCTION SetProperty(objecttoken BINARY_INTEGER, propertyname VARCHAR2, newvalue 
ANY PL/SQL DATATYPE, datatype VARCHAR2) RETURN BINARY_INTEGER;

where:

objecttoken

is the object token of an OLE Automation object previously created by CreateObject.

propertyname

is the property name of the OLE object to set to a new value.

newvalue

is the new value of the property. It must be a value of the appropriate datatype.

datatype

explicitly specifies the datatype of the value passed in. 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.

Code Sample

RangeToken  binary_integer:=-1;
hresult           binary_integer;

/* 
 Previously, RangeToken has been initialized to a valid object token with a 
property by the name of value.
*/
hresult:=ORDCOM.SetProperty(RangeToken, 'Value', `EmpNo', `BSTR');
IF hresult=-1 THEN
  /* Do error checking here */
  return hresult;
END IF;

InitArg

Initializes the parameter set to pass to an Invoke call.

Syntax

PROCEDURE InitArg();

Remarks

Each SetArg or SetPtrArg procedure sets the nth parameter value. The InitArg call initializes the parameter set. After InitArg has been called, a SetArg or SetPtrArg call sets the first parameter to the specified value. A second SetArg or SetPtrArg call sets the second parameter in the parameter list. Subsequent calls set the nth parameters in the parameter list, where n is the number of times SetArg or SetPtrArg has been called after an InitArg call. Another call to InitArg resets the argument list and a call to SetArg or SetPtrArg sets the first parameter again.

Code Sample

See "Invoke" for sample code.

SetArg

Used to construct the parameter list for the next Invoke call. SetArg sets a parameter's value to be passed by value.

Syntax

PROCEDURE SetArg(paramvalue ANY PL/SQL DATATYPE, datatype VARCHAR2);

where:

paramvalue

is the value of the parameter to be passed to an Invoke call. The parameter set is the nth parameter in the parameter list, where n is the numbers of times SetArg or SetPtrArg has been called after an InitArg call.

datatype

explicitly specifies the datatype for the value passed in. 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

Each SetArg or SetPtrArg procedure sets the nth parameter value. The InitArg call initializes the parameter set. After InitArg has been called, a SetArg or SetPtrArg call sets the first parameter to the specified value. A second SetArg or SetPtrArg call sets the second parameter in the parameter list. Subsequent calls set the nth parameters in the parameter list, where n is the number of times SetArg or SetPtrArg has been called after an InitArg call. Another call to InitArg resets the argument list and a call to SetArg or SetPtrArg sets the first parameter again.

Code Sample

See "Invoke" for sample code.

SetPtrArg

Constructs the parameter list for the next Invoke call. SetPtrArg sets a parameter's value to be passed by reference.

Syntax

PROCEDURE SetPtrArg(paramvalue ANY PL/SQL DATATYPE, datatype VARCHAR2);

Remarks

Code Sample

See "Invoke" for sample code.

Invoke

Calls a method of an OLE Automation object. This function uses the parameter list, previously created by the calls to InitArg, SetArg, and SetPtrArg, as input for the OLE Automation method.

Syntax

FUNCTION Invoke(objecttoken BINARY_INTEGER, methodname VARCHAR2, argcount 
BINARY_INTEGER, returnvalue OUT ANY PL/SQL DATATYPE) RETURN BINARY_INTEGER;

where:

objecttoken

is the object token of an OLE Automation object previously created by CreateObject.

methodname

is the method name of the OLE Automation object to call.

argcount

is the number of arguments passed to the OLE Automation object method.

returnvalue

is the return value of the method of the OLE Automation object. If specified, it must be a local variable of the appropriate datatype.

Remarks

If the method's return value is an OLE object, then the developer must specify a local variable of datatype binary_integer for the returnvalue parameter. An object token is stored in the local variable, and this object token can be used with other Oracle COM Automation feature APIs.

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

Code Sample

xpos binary_integer;
ypos binary_integer;
width binary_integer;
height binary_integer;
WorkSheetToken  binary_integer:=-1;
ChartObjectToken binary_integer:=-1;
ChartObject  binary_integer:=-1;
hresult  binary_integer;

/*WorkSheetToken has been initialized with a valid OLE Automation object */

/* Executes a method that takes 0 arguments */
ORDCOM.InitArg();
i:=ORDCOM.Invoke(WorkSheetToken, 'ChartObjects', 0, ChartObjectToken);

/* Executes a method that takes 4 arguments */
ORDCOM.InitArg();
ORDCOM.SetArg(xpos,'I2');
ORDCOM.SetArg(ypos,'I2');
ORDCOM.SetArg(width,'I2');
ORDCOM.SetArg(height,'I2');
i:=ORDCOM.Invoke(ChartObjectToken, 'Add', 4, ChartObject);

OLE Automation Errors

The following is a list of OLE Automation errors and their common causes.

(0x800401f3)
Invalid class string

Cause: The specified ProgID or CLSID is not registered as an OLE object in the registry of the local computer.

(0x8007007e)
The specified module could not be found

Cause: The specified OLE object is registered as an in-process OLE server (.DLL file), but the .DLL file could not be found or loaded.

(0x80020004)
Parameter not found

Cause: A named parameter was specified before a positional parameter.

Action: Ensure that all named parameters are specified after all positional parameters.

(0x80020005)
Type mismatch

Cause: The datatype of a PL/SQL local variable used to store a returned property value or a method return value did not match the Visual Basic data type of the property or method return value.

Action: Ensure that the local variable is of the appropriate datatype. Or, the return value of a property or a method was requested, but it does not return a value.

(0x80020006)
Unknown name

Cause: The specified property or method name was not found for the specified object.

(0x80020008)
Bad variable type

Cause: The datatype of a PL/SQL value passed as a method parameter did not match the Microsoft Visual Basic data type of the method parameter, or a NULL value was passed as a method parameter.

Action: Ensure that any local variables used as method parameters are of the appropriate datatype and are set to a value other than NULL.

(0x80080005)
Server execution failed

Cause: The specified OLE object is registered as a local OLE server (.EXE file), but the .EXE file could not be found or started.

progid	is the programmatic identifier (progID) of the OLE Automation object to create. This character string describes the class of the OLE Automation object and has the following form: OLEComponent.Object OLEComponent is the component name of the OLE Automation server, and Object is the name of the OLE Automation object. The specified OLE Automation object must be creatable and must support the IDispatch interface.
reserved	Currently, this parameter is reserved for future use. Pass a value of 0. Future versions of Oracle COM Automation feature may use this parameter.
servername	is the name of the remote DCOM server on which to instantiate the COM object. Passing a specified name forces Oracle COM Automation feature to attempt to instantiate the COM object on a remote computer. Passing an empty string, for example, `', forces Oracle COM Automation feature to check the registry for the location of the COM object. The registry contains information as to whether the COM object is local or remote. Therefore, to create a local COM object, always pass an empty string and ensure the registry indicates that the COM object exists locally. The registry information for COM objects can be configured with the tool DCOMCNFG.EXE.
objecttoken	is the returned object token. It must be a local variable of datatype binary_integer. This object token identifies the created OLE Automation object and is used in calls to the other Oracle COM Automation feature APIs.

source	is the source of the error information. If specified, it must be a local char or varchar variable. The return value is truncated to fit the local variable if necessary.
description	is the description of the error. If specified, it must be a local char or varchar variable. The return value is truncated to fit the local variable if necessary.
helpfile	is the Help file for the OLE Automation object. If specified, it must be a local char or varchar variable. The return value is truncated to fit the local variable if necessary.
helpid	is the Help file context ID. If specified, it must be a local int variable.

objecttoken	is the object token of an OLE object previously created by CreateObject.
propertyname	is the property name of the OLE object to return.
argcount	is the index of the property array. If the property is not an array, then the developer should specify 0.
propertyvalue	is the returned property value. The returned property type depends on the OLE Automation type that is returned. You must pass the PL/SQL datatype that corresponds to the Microsoft Visual Basic datatype of the OLE Automation property. Otherwise, the OLE Automation feature will not properly convert the Microsoft Visual Basic datatype.
any PL/SQL datatype	supported by COM Automation Feature.

objecttoken	is the object token of an OLE Automation object previously created by CreateObject.
propertyname	is the property name of the OLE object to set to a new value.
newvalue	is the new value of the property. It must be a value of the appropriate datatype.
datatype	explicitly specifies the datatype of the value passed in. 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

objecttoken	is the object token of an OLE Automation object previously created by CreateObject.
methodname	is the method name of the OLE Automation object to call.
argcount	is the number of arguments passed to the OLE Automation object method.
returnvalue	is the return value of the method of the OLE Automation object. If specified, it must be a local variable of the appropriate datatype.

4 Oracle COM Automation Feature Core Functionality

Oracle COM Automation Feature Core Functionality

Developing Solutions Using Oracle COM Automation Feature

Datatype Conversion

HRESULT Return Codes

PL/SQL Application Programming Interfaces

CreateObject

Syntax

Remarks

Code Sample

DestroyObject

Syntax

Remarks

Code Sample

GetLastError

Syntax

Remarks

Code Sample

GetProperty

Syntax

Remarks

Code Sample

SetProperty

Syntax

Remarks

Code Sample

InitArg

Syntax

Remarks

Code Sample

SetArg

Syntax

Remarks

Code Sample

SetPtrArg

Syntax

Remarks

Code Sample

Invoke

Syntax

Remarks

Code Sample

OLE Automation Errors

4
Oracle COM Automation Feature Core Functionality