4
Oracle COM Automation 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

• PL/SQL Application Programming Interfaces

• Oracle COM Automation Errors

• 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:

1. Call CreateObject to create the COM object.

2. 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.

3. 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.

4. Call GetLastError to get the most recent error information.

5. Call DestroyObject to destroy the object.

Developing Solutions Using Oracle COM Automation

Oracle COM Automation feature enables you to use components that provide additional functionality that PL/SQL does not support. You can choose to build your own custom components or use the thousands of pre-built components that are available from third-party ISVs.

Information Required for COM Objects

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.

1. 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.

2. 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);
   

OLE/COM Object Viewer

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.

Figure 4-1 OLE/COM Object Viewer

Text description of the illustration olecom.gif

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.

Table 4-1, "PL/SQL to Visual Basic Datatypes" shows the conversion from PL/SQL datatypes to Microsoft Visual Basic datatypes:

Table 4-1 PL/SQL to 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

Table 4-2, "Visual Basic to PL/SQL Datatypes" shows the conversion from Microsoft Visual Basic datatypes to PL/SQL datatypes:

Table 4-2 Visual Basic 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

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.

See "GetLastError" and "OLE Automation Errors" for 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 is 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.

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

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 BINARY_INTEGER) 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;
ChartToken    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);

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.

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);

Oracle COM Automation Errors

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

COM-0001: Not a boolean type

Action: Ensure that the variable is of the appropriate datatype.

COM-0002: Invalid Token or no interface for token

Action: Ensure that the interface exists.

COM-0003: Maximum Objects reached

Action: Make sure that objects are destroyed after they are used by calling DestroyObject.

COM-0004: The registered CLSID for the ProgID is invalid.

Action: Check that the COM component of the specified ProgID is registered.

COM-0005: An error occurred writing the CLSID to the registry.

Action: Make sure your registry can be written to and is not corrupted.

COM-0006: A specified class is not registered in the registration database

Action: Make sure the class is registered.

COM-0007: Failed to initialize OLE Automation object

Action: Make sure the object is registered as a COM Automation object.

COM-0008: No interface is supported

Action: Check that the interface specified is valid.

COM-0009: Failed to get type info count

Action: Check that the object is properly registered.

COM-0010: Does not support type info implementation

Action: Check that the object is properly registered.

COM-0011: Failed to get type information

Action: Check that the object is properly registered.

COM-0012: Failed to get type attributes.

Action: Check that the object is properly registered.

COM-0013: Failed to get function description at index.

Action: Check that the object is properly registered.

COM-0014: Failure to invoke

Action: Check that the method name is valid for the object.

COM-0015: Bad parameter count

Action: Make sure the number of parameters for a method is equal to the count.

COM-0016: One of the arguments in rgvarg is not a valid variant type.

Action: Check that the object is properly registered.

COM-0017: The application needs to raise an exception. The structure passed in pexcepinfo should be filled in.

Action: Make sure structure in pexcepinfo is initialized.

COM-0018: The requested member does not exist, or the call to Invoke tried to set the value of a read-only property.

Action: Make sure the property value can be written to or the member exists.

COM-0019: This implementation of IDispatch does not support named arguments.

Action: Do not use named arguments. Use standard parameter passing.

COM-0020: One of the arguments in rgvarg could not be coerced to the specified type.

Action: Make sure that the coerced arguments are of compatible data types.

COM-0021: One of the parameter dispatch IDs does not correspond to a parameter on the method.

Action: Make sure the arguments are passed in correctly.

COM-0022: One or more of the arguments could not be coerced.

Action: Make sure your arguments are compatible.

COM-0023: The interface ID passed in riid is not IID_NULL.

Action: Make sure the interface ID passed is IID_NULL.

COM-0024: The member being invoked interprets string arguments according to an unrecognized locale ID (LCID).

Action: Make sure your localeID is valid.

COM-0025: Not an optional parameter

Action: Make sure your argument count is correct for the number of parameters passed in.

COM-0026: Name exceeded the maximum character allowed

Action: Enter 1024 characters or less for the name.

COM-0027: This class cannot be created as part of an aggregate.

Action: Do not create this class as part of an aggregate.

OLE Automation Errors

The following is a list of OLE Automation errors and their common causes. Both the hexadecimal and binary error codes are listed.

(0x800401f3) (-2147221005) Invalid class string

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

(0x8007007e) (-2147024770) 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) (-2147352572) 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) (-2147352571) 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) (-2147352570) Unknown name

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

(0x80020008) (-2147352568) 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) (-2146959355) 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.