Enhancing Your Forms Applications with OLE 2.0

Table of Contents

INTRODUCTION
WHAT IS OLE?
OLE SUPPORT IN FORMS
CREATING THE OLE CONTAINER
THE "VALUE" OF THE OLE CONTAINER
LINKING AND EMBEDDING
OLE OBJECT ACTIVATION STYLES
MANIPULATING THE OBJECT
SETTING OLE PROPERTIES
Activation Properties
Popup Menu Properties
OLE Tenant Properties
CONTROLLING OLE OBJECTS FROM FORMS
The FORMS_OLE Package
The OLE2 Package
EXAMPLES
ACCESSING CELLS IN AN EXCEL SPREADSHEET
HIDING THE OLE POPUP MENU
CONCLUSION

Introduction

How many times have you wished you can have a spreadsheet in your form?

How many times have you wished you can have a spell checker you can use in your form?

How many times have you wished you can have ...

The list goes on and on.

In Forms 4.5, YOU CAN.

This paper describes in detail what OLE (Object Linking and Embedding) is, and how you can exploit the OLE 2.0 technology in your Developer/2000 applications using pre-built OLE 2.0 components.

What is OLE?

OLE is a technology that enables independent software vendors to build software components (interoperable objects) that adhere to a specific interface. The power of OLE lies in the fact that these software components from one vendor can be plugged into software components of other vendors seamlessly.

With OLE, developers no longer build the entire application from scratch. They can easily incorporate specialized software components from multiple vendors in their applications, enabling them to deliver systems in a shorter time and at a lower cost.

OLE provides a mechanism for creating compound documents using interoperable objects. A compound document is a document that contains OLE objects like spreadsheets, images, sounds and charts, as well as other compound documents.

These compound documents are created and managed by container applications. The container does not know the specific kinds of object it contains but simply provides the storage for them and initiates their creation.

The server application, on the other hand, is responsible for the creation, storage and manipulation of OLE objects. For example, it decides how to repaint the object when certain portions are exposed.

The following figure shows a Word document as an OLE container with two embedded OLE objects, as well as a Form application as a container with an embedded Excel spreadsheet object.

Figure Examples of OLE containers with OLE objects

OLE objects are defined in the registration database, or registry, so that they can be used across applications. This registration is done as part of the OLE server application installation.

Through the use of OLE, the functionality of different object applications are integrated in a single application. This allows end users to interact with that one application and concentrate on their tasks instead of being concerned with switching between the various object applications. This new model of computing is more “document-centric” and less “application-centric”, thereby allowing end users to be more productive.

OLE Support in Forms 4.5

Forms 4.5 is an OLE container which allows you to incorporate various OLE objects in a form module. For example, if you had a Geographic Information System (GIS) OLE2 server, you could embed this within a Form application.

You could use all the functionality of the GIS application, such as displaying and manipulating maps, as well as using Forms' features for manipulating database data. You can even pass data queried in the form to this map system and have the map display coordinate with data in the form or vice versa.

This provides you with the capabilities of both the GIS mapping application and Forms within a single application, without requiring the user to learn two interfaces, or to switch between different applications, for the single task.

Creating the OLE Container

From the Layout Editor in the Forms Designer, use the OLE tool to create an OLE container in your form module. When it is first created, its properties are set to some default values. You can change the properties to suit your needs as described in the section “Setting OLE Properties”.

Move the mouse cursor over the OLE container, and press the right mouse button to bring up the OLE popup menu to insert an OLE object into the container. The following figure shows the dialog box for inserting OLE objects.

Figure 2 Inserting OLE objects

Selecting “Create New” will bring up the associated OLE server where you can create the OLE object from scratch. Exiting the OLE server will cause the object that you just created be inserted into the container. Selecting “Create from File” and typing in an existing OLE object file will insert it into the container.

The “Value” of the OLE Container

At design time, you can insert an object into a container manually. This becomes the template object (initial value) of the container at runtime.

During runtime, you can also manually insert an object into the container. If the container is a base table item, you can query the object from or save the object into the database. The corresponding column in the database must be of type LONG RAW.

As an analogy, the template OLE object within a container at design time is just like the default value for a text item. Just as you can assign a default value for a text item, you can initialize an OLE container with a Microsoft Word letter template so that your users simply fill in the contents of the letter for each customer at runtime and save them in the database.

Linking and Embedding

OLE objects possess two kinds of information:

• Presentation data which tells how the object should render itself on a display.
• Native (source) data which contains information needed by the server application to edit the object.

There are two ways you can incorporate a template OLE object in a form at design time:

• Embedded Object

An embedded object has both its presentation and native data stored in the form module.

• Linked Object

A linked object only has presentation information and a reference to its native data stored in the form, while the actual native data is stored separately in a file on disk.

The decision on whether to embed or link the template object of the OLE container depends on how that template object is maintained and accessed, and if there are storage constraints.

You should embed the template object when:

• Only one form module is using the template object and the users do not need to modify that object outside of forms.
• You only want to maintain that one form module instead of having to take care of the individual source file of each template object.
• You want to edit the template object in-place. Refer to the section “OLE Object Activation Styles” for an explanation of in-place activation.
• The size of the form module is not a big concern because an embedded template object lives entirely within the form module, thus increasing the size of the form module.

You should link the template object when:

• The template object needs to be accessed outside of the form module.
• The template object serves multiple container applications.
• The size of the form module is a concern.

OLE Object Activation Styles

An OLE object can be activated in one of two styles.

• In-place Activation

Also known as “visual editing”, this lets users manipulate the object within the form application without switching to a different window.

During in-place activation, the activated object appears inside a hatched border, and the toolbar, menu and other controls of the activated object temporarily replace those of the form application. When the user returns to the form application, its toolbar and menu are displayed again.

This is only available for embedded OLE objects.

Figure 3 In-place Activation

• External Activation

This lets users manipulate the object in a separate window opened by that object's server application. Because of this, the form application's toolbar and menu remain intact during external activation.

This is available for both embedded and linked objects.

Figure 4 External Activation

Manipulating the Object

There are two ways of interacting with an OLE object during runtime.

• Direct Manipulation

Users can interact with the object using various activation styles at runtime. For example, the user can double click on the object to activate it. These activation styles are described in more detail in the section “Activation Properties”.

• OLE Automation

OLE server applications expose a set of commands that lets users manipulate it programmatically. You can access this set of commands from your form application using two PL/SQL packages, FORMS_OLE and OLE2. These will be discussed in the section “Controlling OLE Objects from Forms”.

Setting OLE Properties

There are three categories of properties for an OLE container.

• Activation Properties
• Popup Menu Properties
• OLE Tenant Properties

Activation Properties

The following properties determine how an OLE object is activated.

• OLE Activation Style

This specifies how the user will activate the OLE object. The valid settings are:

• OLE In-place Activation

This specifies whether in-place activation can be used for editing embedded OLE objects.

• OLE Do In Out

This specifies if the OLE server of the embedded object provides inside-out support. When an object supports this, it is always active in the sense that the user need not explicitly activate it in order to interact with it.

For example, if we have an application that has an embedded spreadsheet which does not support OLE Do In Out and an embedded bar chart which supports this feature. The user will need to activate the spreadsheet first before being able to change the values of cells in the spreadsheet. On the other hand, the user can simply click on a bar in the chart directly and change its color without first activating it.

Popup Menu Properties

• Show OLE Popup Menu

This specifies whether the right mouse button can be used to display the OLE popup menu.

• OLE Popup Menu Items

This determines which popup menu items are displayed and enabled when the right mouse button is depressed over the OLE object. This requires the Show OLE Popup Menu property be set to true. The specific action for each menu item is described below:

You can set the Display and Enable properties of each of these menu items in the Forms Designer, as well as set or access these two properties programmatically using the set_item_property and get_item_property built-ins respectively. The Display property specifies if the menu item is displayed. The Enable property specifies if a displayed item appears dimmed or grayed out.

Figure 5 OLE Popup Menu

OLE Tenant Properties

• OLE Class

This specifies the class of OLE objects that can reside in the container. The values are derived from the OLE server applications' information stored in the registration database. A null value signifies that any kind of OLE object can be inserted into the container.

• OLE Resize Style

This specifies how the OLE object is displayed in the container. The valid settings are:

• OLE Tenant Aspects

This specifies how the OLE object looks like in the container. The object can be displayed entirely, as an icon, or as a thumbnail.

• OLE Tenant Type

This specifies the type of object that can reside in the container. The valid settings are:

• Show OLE Tenant Type

This specifies whether a border surrounds the OLE container. The type of border is dependent on the object type.

Controlling OLE Objects from Forms

Forms provides two packages for you to interact with the OLE item. These packages are described in detail below. The next section shows some examples illustrating their usage.

The FORMS_OLE Package

This package contains built-ins for interfacing with the OLE item. They can be divided into two categories as described below. You should refer to the Forms 4.5 Reference Manual Volume 1 for the exact arguments to be passed for each built-in.

The following built-ins manage the server:

The following built-ins control the container:

The OLE2 Package

This package contains object types and built-ins for manipulating the OLE object. They can be divided into several categories as described below. You should refer to the Oracle Procedure Builder Developer's Guide for the exact arguments to be passed for each built-in.

The following are the new object types:

The following built-ins manage the object:

The following built-ins manage the argument lists:

The following built-ins retrieve and set the object's properties respectively:

The following built-ins call the methods exposed by the server application:

The following built-in returns an OLE exception:

Examples

This section shows some examples of using these two packages to manipulate an object.

Accessing Cells in an Excel Spreadsheet

This example involves setting and getting cells in an Excel spreadsheet. We first need to create an OLE2 container. For this example, let us call it SPREADSHEET.

Since we will need to get and set multiple spreadsheet cells, we should put these routines in a PL/SQL package. We will also need to store the object handle to the spreadsheet in a package variable. For this example, let us call it OBJ_HND.

Before we can send commands to an OLE object, we need to first activate the associated server and obtain the handle to the OLE object. The following package shows the code for this example.

package spreadsheet is

procedure setcell(trow number, col number, val number);

function getcell(trow number, col number) return number;

end;

package body spreadsheet is

obj_hnd ole2.obj_type; /* store the object handle */

function get_object_handle return ole2.obj_type is

begin

/* If the server is not active, activate the server

and get the object handle.

*/

if not forms_ole.server_active ('spreadsheet') then

forms_ole.activate_server('spreadsheet');

obj_hnd := forms_ole.get_interface_pointer('spreadsheet');

end if;

return obj_hnd;

end;

/*

Excel cells are accessed with the following syntax in Visual Basic:

ActiveSheet.Cells(row, column).Value

In PL/SQL, we need to first get the active sheet using the forms_ole.get_interface_pointer built-in. We can then use that to call the Cells method with the row and column in an argument list to get a handle to that specific cell. Lastly, we access the value of that cell.

*/

procedure setcell(trow number, col number, val number) is

d ole2.obj_type;

c ole2.obj_type;

n number;

lst ole2.list_type;

begin

/* Activate the server and get the object handle

to the spreadsheet.

*/

d := get_object_handle;

/* Create an argument list and insert the specified

row and column into the argument list.

*/

lst := ole2.create_arglist;

ole2.add_arg(lst,trow);

ole2.add_arg(lst,col);

/* Call the Cells method to get a handle to the

specified cell.

*/

c := ole2.invoke_obj(d,'Cells',lst);

/* Set the value of that cell. */

ole2.set_property(c,'Value',val);

/* Destroy the argument list and the cell object

handle.

*/

ole2.destroy_arglist(lst);

ole2.release_obj(c);

end;

function getcell(trow number, col number) return number is

c ole2.obj_type;

d ole2.obj_type;

n number;

lst ole2.list_type;

begin

/* Activate the server and get the object handle

to the spreadsheet.

*/

d := get_object_handle;

/* Create an argument list and insert the specified

row and column into the argument list.

*/

lst := ole2.create_arglist;

ole2.add_arg(lst,trow);

ole2.add_arg(lst,col);

/* Call the Cells method to get the value in the

specified cell.

*/

c := ole2.invoke_obj (d,'Cells',lst);

n := ole2.get_num_property (c, 'Value');

/* Destroy the argument list. */

ole2.destroy_arglist(lst);

ole2.release_obj(c);

return n;

end;

end;

To access a cell, we simply write the following code:

spreadsheet.set_cell(3, 5, 91.73);

:block1.item1 := spreadsheet.get_cell(2, 4);

Hiding the OLE Popup Menu

This example shows you how to hide the right mouse menu of the OLE object at runtime and use some buttons to control the OLE object instead. Suppose we want to create the following screen where clicking on the WORD or EXCEL button will load a Word or Excel document, and enable the buttons OPEN and EDIT depending on whether these verbs are exposed by that server.

Figure 6 Sample Screen for Hiding OLE Popup Menu

Create the OLE container and buttons as shown in the figure. For this example, let us name the OLE container OLEOBJ and turn the Show OLE Popup Menu property off.

You will need to create a parameter to hold the current button's name. For this example, let us name it CURRITEM. This will store the name of the button for which the verb is being determined if it is exposed by the server. This can be of character type of length 30.

You will also need the triggers below. The following trigger initializes the OLE container and enables/disables the OPEN and EDIT buttons. A similar trigger needs to be created for the EXCEL button.

WHEN-BUTTON-PRESSED trigger on WORD button

declare

t varchar2(20);

begin

/* Initialize the container with the specified object. */

forms_ole.initialize_container ('oleobj', 'c:\ole\word.doc');

/* For each button to replace the right mouse menu, set the

parameter CURRITEM to be the name of that button, and use

forms_ole.find_ole_verb to determine if that verb is

exposed by the server.

If it is, enable the button. Otherwise, an error

FRM-41345 will occur. This error will be trapped by

the form-level ON-ERROR trigger, which will disable the

button.

*/

:parameter.curritem := 'CONTROL.OPEN';

t := forms_ole.find_ole_verb('oleobj','Open');

if t > 0 then

set_item_property(:parameter.curritem, enabled, property_true);

end if;

:parameter.curritem := 'CONTROL.EDIT';

t := forms_ole.find_ole_verb('oleobj','Edit');

if t > 0 then

set_item_property(:parameter.curritem, enabled, property_true);

end if;

end;

WHEN-BUTTON-PRESSED trigger on EDIT button

forms_ole.exec_verb ('oleobj', 'Edit');

WHEN-BUTTON-PRESSED trigger on OPEN button

forms_ole.exec_verb ('oleobj', 'Open');

Form-level ON-ERROR trigger

declare

errcode number(5) := message_code;

errtype char(3) := message_type;

errtext char(78) := message_text;

begin

/* If verb is not defined, disable the button. */

if errcode = 41345 then

set_item_property(:parameter.curritem, enabled, property_false);

else

message(errtype||'-'||to_char(errcode)||': '||errtext);

end if;

end;

Conclusion

This paper explains what OLE is and the benefits of this new technology. It describes in detail how you can create and manipulate OLE objects in Forms 4.5 using the various properties and PL/SQL packages.

With OLE, you can quickly take advantage of the functionality of the many software components from the market and deliver Developer/2000 applications in a shorter time.