• 32
• Oracle Forms 4.5
• The Oracle Forms Environment
• The Object Navigator
• Triggers
• Alerts
• Attached Libraries
• Blocks
• Items
• Relationships
• Canvas-Views
• Editor
• LOVs
• Object Groups
• Parameters
• Program Units
• Property Classes
• Record Groups
• Visual Attributes
• Windows
• Object Properties Sheets
• The Layout Editor
• Customizing the Forms Environment
• Creating New Forms Modules
• Building a Simple Default Form
• Using Boilerplate Text and Graphics to Enhance Forms Applications
• Customizing Forms with Properties
• Implementing Triggers and Program Units
• Using Record Groups and Lists of Values for Data Validation
• Using Relationships to Link Separate Blocks in a Form
• Mouse Events, Timers, and Other Advanced Widgets
• Working with the Mouse
• Working with Alerts
• Using Timers for Event Control
• Implementing VBX Controls
• Considerations for Multiple Form Applications
• Techniques for Dynamic, Runtime Applications
• Working with Menus
• Libraries, Object Classes, and Visual Attributes
• Quality Considerations for Oracle Forms
• Summary

32

Oracle Forms 4.5

As stated in the previous chapter, Oracle Forms 4.5 is a development tool that can be used to create applications to enter, access, change, or delete data from an Oracle database in an online, form-based environment. Forms has provided many objects for developing an effective application module. Coupled with the concept of user extensibility, Forms can be used to serve virtually every online need in an Oracle database environment.

This chapter will explore many of the concepts related to Oracle Forms development. The examples will start with a few baby steps and gradually add enough information so that you will be able to walk on your own. With each advance in complexity, the applications will become more powerful. By the end, you will be able to delve further into many advanced concepts and perhaps add innovations of your own.

The Oracle Forms Environment

Oracle Forms 4.5 operates in a graphical user interface (GUI) environment such as Microsoft Windows 3.x. The primary tools used to develop customized forms are the Object Navigator, the Layout Editor, and the object property sheets. In addition to online forms, Oracle Forms 4.5 is also used to create and maintain application menus and program unit libraries.

The Object Navigator

The Object Navigator (shown in Figure 32.1) presents all the elements that combine to create an Oracle Forms application module. These elements (or objects) may be manipulated using the iconic buttons and menu options. Within the Object Navigator, the application components are presented to the developer in a hierarchical outline format indicating the organization of the form elements. The individual element groupings are described in the following subsections.

Figure 32.1. The Oracle Forms Object Navigator and property sheets.

Triggers

Triggers are PL/SQL functions that will be executed based on some specific activity or condition in the form. These activities, called events, are the foundation of Oracle Forms' user extensibility feature. While many of the trigger events will perform a particular default function, a trigger can be written to disable, modify, or enhance the default processing capabilities associated with the event. Triggers can be defined at the form, block, record, or individual field level. In addition, a trigger may cause another trigger to "fire" as well as execute another related program unit.

Alerts

Alerts are used to provide additional information or other messages that require response or acknowledgment from the user. An alert may contain one to three buttons that can be used to define the action the user wants to execute based on the condition.

Attached Libraries

Libraries are special Oracle Forms modules that can be defined to contain reusable procedures and functions. By attaching program unit libraries to a form, the program code can be maintained in a single file rather than in each individual form module. Libraries help to assure the quality of an application by ensuring that all modules follow the same set of business rules.

Blocks

A block in a form generally corresponds to an individual entity (table, view, or snapshot) in the database. Additionally, blocks may be created that do not correspond to any table. These blocks, called non-base table blocks, are usually used to hold generic control information, such as query criteria, counters, conditional indicators, and other information that may relate to more than one record or activity. (Blocks that relate to an entity are referred to as base table blocks.)

Items

Items (which are not shown in Figure 32.1) are grouped in the Object Navigator within their respective blocks. An item corresponds to a single data element or field. These items may contain database columns or may be used as containers for other related data.

Relationships

Relationships (not shown in Figure 32.1) are defined according to how the separate blocks in a form relate to each other. Typically in a multiple block form, one block is usually defined as the primary or master table, and the other blocks display detail information associated with the current record in the master block. An example of a master-detail form would be a customer record application where the master block shows the customer name and address and the detail block will display the history of purchases made by the customer in multiple records.

Canvas-Views

A canvas is the virtual structure where the form objects are laid out. For the most part, canvases are defined as either content or stacked canvases. A content canvas is displayed in its entirety within the form window. Whenever a content canvas is first displayed, any other canvases will be hidden in the window. A stacked canvas, on the other hand, will display "on top" of any existing canvases within the form window. In addition to these two canvas types, a third canvas type has been included in Forms 4.5: a button bar canvas. A button bar canvas will appear on top of all displayed canvases and is usually used to contain iconic buttons for user commands. A special null canvas exists to contain objects that are not displayed.

Editor

An editor is a window for viewing and maintaining large data fields. These fields are sometimes included for the entry of user comments or other information that would not normally fit in a displayed item.

LOVs

LOVs (also called lists of values) provide the user with a list of valid entries for a field. A list of values presents data contained within an object called a record group whereby the user will select one value from the list to populate a form item. The list of values may also be used to validate user input to ensure that a valid value is entered.

Object Groups

An object group is a special mechanism for packaging several form objects into a container, which may then be used in other forms with a minimum of programming. Once an object group is created in a form, any of the form objects may be copied into the object group. For example, a form may contain a secondary page showing a scheduling template. The components of this submodule may be copied to the object group and other forms may reference the object group, which will behave as if it were part of the second form itself. This is another feature of Forms that promotes the object-oriented approach while assuring quality through consistency.

Parameters

Parameters may be defined for a form in order to provide a startup input for the form. Typically, parameters are used to pass values from one form to a newly called form in a multiple-form application. Prior to the Developer/2000 tools, the only way to pass values between forms was through the use of global variables that tended to use up available memory. While global variables are still available, parameters should be used in cases where the data is specific to the individual forms rather than universal to all forms in an application.

Program Units

Program units are PL/SQL procedures and functions that can be called by the form triggers. A program unit is equivalent to the third generation programming concept of subroutines. A typical candidate for definition of a program unit is a segment of program code that is used by more than one trigger. Program units should also be used to modularize long code segments.

Property Classes

A property class defines the properties of a class of objects. In cases where many form objects have identical properties, a property class benefits the developer by ensuring that the objects are consistent. Additionally, property classes can be used to enforce standards and to accelerate the development time for a form module.

Record Groups

A record group can be thought of as a virtual table in memory. Record groups are structured sets of data that can be used to pass data between application modules or to populate lists of values or other list items.

Visual Attributes

An object's visual attribute defines the color, font, and style characteristics for an item. While each of these values can be set individually for each object, the visual attribute provides a mechanism to define a valid combination of visual characteristics for a form. A high-quality application should not use many different visual attributes within the items. By adhering to a set of visual attributes, the forms may use an item's particular characteristics to signify a specific meaning.

Windows

A window is the frame within which a form appears on the user's screen. Each canvas is assigned to a specific window in the form and several canvases may be assigned to the same window. A single form can contain several windows or it may consist of only one window.

Object Properties Sheets

The characteristics of each element in a form are defined by its various properties. A context-sensitive property sheet exists for each component, based on the type of object that is being defined. Part of the property sheet associated with the module definition is shown in Figure 32.1 in the section describing the Object Navigator. These properties are grouped logically within the property sheet and each type of object has a different property sheet. Several of the more important properties will be covered later in this chapter.

The scroll bar at the right side of the property sheet is used to navigate through the property sheet, and the iconic buttons at the top are used to control activities in the sheet.

The first two buttons copy and paste all of the properties between the property sheet and the clipboard. The next two buttons are used to add and delete custom properties in the property sheet. The next button is used to create a new property class based on the properties for the object.

Next, the Inherit button causes the currently selected property to be inherited from the object's property class. If no class is defined for the object, the Inherit button will cause the property to be restored to the Forms 4.5 default for the property.

The next button is used when more than one object has been selected from the Object Navigator. The button toggles between showing the union of all properties associated with all of the selected objects or only the properties associated with all of the objects (intersection). By selecting multiple objects in the Object Navigator, a single change to a property in the property sheet will be propagated to all of the objects.

Finally, the last button is used to freeze or unfreeze the property sheet synchronization mode. When this window is unfrozen (the default), whenever a new object is selected, the property sheet will be synchronized to show the new object. However, if it is frozen, the synchronization will not occur until it is unfrozen.

The Layout Editor

The Layout Editor (shown in Figure 32.2) presents the canvas on which the form objects are laid out. This tool is operated through the use of iconic buttons and menu choices. The buttons along the left side of the Layout Editor window are used to create and manipulate the form layout objects. These buttons (top to bottom, left to right) are described in Table 32.1.

Figure 32.2. Oracle Forms Layout Editor.

Table 32.1. Layout Editor Buttons.

Button

Usage

The list boxes at the top of the Layout Editor allow the user to navigate between the multiple canvases and blocks in the form, while the iconic buttons are used for command control within the editor. These buttons, in order are:

• Open Opens an existing module.
  
  
• Save Saves the current module.
  
  
• Run Runs the current module.
  
  
• Cut Cuts the selected object(s).
  
  
• Copy Copys selected object(s).
  
  
• Paste Pastes object(s) from clipboard.
  
  
• Copy Properties Copies properties to clipboard.
  
  
• Paste Properties Pastes properties from clipboard.
  
  

Customizing the Forms Environment

Oracle Forms 4.5 allows the programmer to modify the development environment using the Tool | Options menu choice. The Designer Options dialog box (shown in Figure 32.3) is used to set the behavior of the Oracle Forms Designer.

Figure 32.3. Defining Oracle Forms designer options.

The five check boxes establish the operating mode for Oracle Forms Designer. If checked, the first box will cause the current module to be saved automatically whenever a form is generated, and the second box will cause the form to be generated (compiled) whenever it is run from the designer. (The net effect of having both boxes checked is that every form will be saved and generated by clicking the run button.)

By default, Oracle Forms displays a hint in the lower-left area of the screen based on the context of the designer. By selecting the next check box, these hints will be suppressed. If the next option. Run Modules Asynchronously, is checked, the Forms Designer enables the developer to run a form module and work in the designer simultaneously. Otherwise, the runtime form must be exited before using the designer further. The last check box defines the editor that should be used in the designer. Checking this box causes the designer to use an operating system editor rather than the default forms editor.

The color palette determines the colors that should be used in a form. It is recommended that the Oracle 16-color palette be used, as shown in the figure, to ensure color-matching capability between form objects. The Color Palette Mode is used to define how color palettes should be handled in Oracle Forms. The options for the list box are: Editable, Read Only - Shared, and Read Only - Private. Editable means that the color palette of the active form will replace the system color palette, causing the active form to be shown accurately, while the appearance of any inactive forms may not be accurate. Read Only - Shared means that each form's color palette will be appended to the system palette until the space reserved for the palette becomes full. If any forms are then opened which use a different color palette, they may not appear accurately. Finally, Read Only - Private operates the same as shared mode except that Oracle Forms assures that the palette used is always valid for the active form, and any inactive forms may not appear correctly because their color palettes have been cleared to make room for the new form.

The module access options define whether modules should be opened from the database or the file system (or both) and what types of files should be included in the selection. The last option defines the printer that should be used for any printing requirements in the Forms Designer.

The second tab in this window (shown in Figure 32.4) is used to define the options to be used when a module is run from within the Oracle Forms Designer. The first option, Buffer Records, will cause Forms to buffer only the minimum number of records (the number of records displayed plus three) in memory. All additional rows retrieved will be stored in a temporary file. The next option causes the form to be executed in debug mode. This option allows the developer to insert break statements in the PL/SQL segments to observe the values in form items and to trace the execution of the program code.

Figure 32.4. Setting Oracle Forms runtime options.

The next four options are related to performance tuning of Oracle Forms. Array processing allows the form to return multiple rows from the database in a single fetch cycle rather than one at a time. This usually causes better performance; however, fetching more than one row at a time may impact the memory usage in the form. In order to maintain backward compatibility, Oracle Forms allows the developer to use Version 2-style triggers (separate processing steps) in the form. The Optimize SQL Processing option causes these triggers to be processed using an optimization technique to take advantage of the more "modern" capabilities that have been incorporated into PL/SQL. Transaction Mode Optimization causes all implicit SQL statements (for example, posting and committing triggers) to optimize cursor usage so that the cursors may be shared within the form. The Statistics option will return statistics regarding cursors and other resource utilization when the form is run. The other effect of this option is that a SQL Trace session will be generated, which can be analyzed by TKPROF or another performance-analysis tool to assist with tuning.

The next option will display the block menu for a form—rather than the form itself—as soon as the form starts up. This menu will allow the developer to navigate directly to a particular block rather than to the initial default. Query-only mode disables any inserts, deletes, or updates in a form. Finally, Quiet mode "turns off" the audible beep that is played whenever a message is generated in the form.

Creating New Forms Modules

A form may be created using the File | New | Form menu choice or by using the create-form hot key (Ctrl+Y). The new form will be added to the Object Navigator. Additionally, whenever the Oracle Forms Designer is started, a new empty form is automatically created. Similarly, program-unit libraries (Ctrl+I) and menu modules (Ctrl+-E) may also be created from the menu. These modules may in turn be saved, either by running them (with the appropriate options selected) or by explicitly executing a save from the File menu or using the iconic button in the Designer windows.

Building a Simple Default Form

Oracle Forms' feature of intelligent defaulting allows the developer to create a basic form in mere minutes. As an example, the Warehouse Maintenance form (shown in Figure 32.5) can be created quickly without writing a single line of program code.

Figure 32.5. Warehouse Maintenance form.

To build this form, first create a new form in the Object Navigator (log into the database if you have not already done so) and select the Blocks group within the new form. The block-definition dialog box will appear as shown in Figure 32.6. This form will be based on the WAREHOUSES table; therefore, enter it into the base table field and navigate to the next field using the Tab key. (Alternatively, the table name can be obtained using the Select button to the right of the table name field. This button will present a dialog box that may be used to list all the available tables in the database.) Notice that by default the name of the block changes to match the table name, although the name can be any value the developer desires. Hit the Tab key again to navigate to the canvas field, which has defaulted to a value such as CANVAS1. Change the name of this field to WH_CANVAS.

Figure 32.6. Block-definition dialog box.

Once this box is completed, click on the Items tab to present the Item-definition dialog box shown in Figure 32.7. To obtain the columns in this table, click on the Select Columns button and all columns in the table will be listed. A plus sign preceding the column indicates that the column will be used in the form. To exclude a column, double-click on the column name in the list box. For each column listed, modify the column label and width as indicated in Table 32.2.

Table 32.2. Warehouse Maintenance Form Column Definitions.

DB Column

Label

Width

Figure 32.7. Default-column definition dialog box.

After the column definition is completed, the form layout should be defined using the Layout tab as shown in Figure 32.8. This form presents multiple rows in a tabular arrangement with the individual rows arranged vertically. The records field representing the maximum number of records displayed should be 8, and there should be 0 spacing between the records. Other options are used to determine whether integrity constraints should be enforced for the form and if a button palette is needed for the table. (The button palette will create a default set of iconic buttons that can be used for table maintenance.) The last checkbox will include a scrollbar in the form that can be used to navigate through the block if all records cannot be displayed at one time.

Figure 32.8. Defining form style and layout.

After this dialog box has been completed, click the OK box to finish constructing the form. To test the form, run it by clicking the Run icon or choose the Run option in the File menu. (Note: This chapter will assume that the Designer options Save Before Generate and Generate Before Run are selected. Otherwise these steps must be done manually prior to running the form.) The form will appear, as shown previously in Figure 32.5, with all of the data boxes empty. To list the existing data, select the Query | Execute menu choice. Data may be entered into the fields and then saved using the Action | Save menu choice, and the Action | Exit menu choice is used to close the form.

Using Boilerplate Text and Graphics to Enhance Forms Applications

The previous example does not provide the three-dimensional look and feel that is common in most Windows software. By utilizing some of Oracle Form's graphical objects and visual effects, you can transform the previous example into a more aesthetically pleasing application, as shown in Figure 32.9.

Figure 32.9. Using boilerplate objects to enhance applications.

To modify this form, first select the block title in the Layout Editor and remove it using the Delete key. Do the same with the box around the data grid. Now, using the select tool, select all of the column headings on the screen. To modify the font, choose the Format | Font menu choice to display the font selection dialog box as shown in Figure 32.10. In this case, choose the Arial font, Bold Italic style, and size 9 (A sample of the selected font will be shown within the dialog box.) and click the OK button to change the fonts.

Figure 32.10. Font-selection dialog box.

Now, position the column headings at the center above the column (defaults to left-justified using the Align Objects tool. To do this, click on the column heading text for the Warehouse Code and then while holding the shift key, click on the code field. Using the Arrange | Align Objects menu choice, choose the alignment options as shown in Figure 32.11. Repeat for all four columns.

Figure 32.11. Align Objects dialog box.

Now, select the Rectangle tool, and draw a rectangle as shown in Figure 32.9 so that it borders both sides and the bottom of the data grid. (Use the sizing anchors to resize and position the rectangle if necessary.) To create the three-dimensional effect, select the Format | Bevel menu choice and select the lowered effect to create the appearance of a lowered block on the screen. The depth may be adjusted using the Format | Line menu choice. Create another smaller rectangle overlapping the previous rectangle for the screen label. Using the Text tool, create the screen label (WAREHOUSES) using an appropriate font. Using the mouse, arrange the objects to appear on the screen as shown in the example.

Customizing Forms with Properties

As previously mentioned, Oracle Forms' intelligent defaulting capabilities can be used to create useful applications. In many cases, however, it is necessary to define specific characteristics for application objects. This can be done by modifying the properties of the form objects using the object property sheets.

To examine properties, again load the form from the previous section. Each object in the form (including the form itself) has an associated context-sensitive property sheet. The properties for the objects are organized in logical groups based on usage. To change a property, select the object in the Object Navigator. If the property sheet is visible in the split window arrangement, the property sheet will automatically display for the object. If the property sheet is not visible, double-click on the icon at the left of the object name in the Object Navigator to view the property sheet. Usually, only some of the properties are visible in the window and a scrollbar is available to view the other properties. To modify a property, click on the property in the property sheet. The value of the property will be copied to the top line of the property sheet window. Edit this line and press return to modify the property.

The first custom modifications to be made will affect the overall application appearance. First select the property sheet for the form window. Change the Window Title to "Warehouse Maintenance Form," then change the window width to 382 and the height to 200. To prevent a user from using the Windows function to resize the window frame or to minimize the form, update the Fixed Size property to True and the Iconifiable property to False for the form window. Additionally, repeat the sizing properties for the WH_CANVAS canvas.

Now, select the WH_CODE item and change the Update Allowed property to False. This change will protect the primary key for the warehouses table from being changed. Finally, select the WH_OPEN_DATE and WH_CLOSE_DATE fields together. Notice that the common property sheet shows that multiple objects have been selected and that in cases where the properties are different, a set of asterisks is shown. In this case, modify the Format Mask property to be MM/DD/YY. As a final step, change the font and sizing for all of the fields in the warehouse record, by selecting all of them and displaying the common property sheet. Modify the height to equal 14 and change the font name to Arial with a size of 8 points and a weight of bold.

Upon completion of these changes, save and run the new form. The result should look like the form shown in Figure 32.12. Compare this form with the form created in the previous section to see how the property changes have affected the resulting form module.

Figure 32.12. Example form demonstrating custom properties.

Implementing Triggers and Program Units

Oracle Forms applications can be further customized by developing PL/SQL procedures and functions called triggers. These triggers are attached to specific activities in the form called events. Events typically are defined as before (PRE-FORM, PRE-QUERY, PREINSERT), after (POST-FORM, POST-QUERY, POST-INSERT), or during (WHEN-NEW-FORM-INSTANCE, WHEN-BUTTON-PRESSED) common database activities. Additionally, triggers can be associated with certain keyboard activities (for backward compatibility with character-based applications), although the trend is to minimize key triggers.

The customer maintenance form shown in Figure 32.13 utilizes a pre-insert trigger to determine a unique customer number based on a sequence generator. Additionally, triggers are defined for the buttons at the bottom of the form.

Figure 32.13. Customer Maintenance form.

To construct this form, create a block for the CUSTOMERS table using a form style in the block layout definition. Arrange the items and boilerplate objects as shown in the figure and using the button tool, create three buttons as shown on the screen (These buttons will be labeled as PUSH_BUTTONx.). Using the Size Objects and Align Objects tools in the Arrange menu, position and size the form objects to appear as shown.

Now, define the object properties as needed by defining the window and canvas sizes and titles. Next, because the customer number should be protected from update, change the Update Allowed and Navigable properties for the CUST_NO item to False. Modify the label properties for the buttons to reflect the text that is shown in the figure.

At this point, the additional processing logic may be added to the form. First, select the triggers group directly below the CUSTOMERS block in the Object Navigator. Click on the Add Object button and a list will appear with the names of all allowable triggers that can be built for the form. The trigger to be built will determine the customer number for a new customer prior to insert based on the database sequence generator. While the list is displayed, either use the scroll bar to find the PRE-INSERT trigger or type the trigger name into the input box.

The PL/SQL editor (shown in Figure 32.14) will now appear, indicating the trigger level and the name of the trigger with an area that may be used to enter the trigger procedure as shown. Once the text has been entered, click the Compile button to make sure that there are no code errors and then click the Close button to complete the trigger definition. Note the colon used to reference form fields. Other buttons can be used to revert to the version prior as of the last close or compilation, to create a new trigger, or to delete the existing trigger.

Figure 32.14. PL/SQL editor for trigger creation.

Finally, WHEN-BUTTON-PRESSED triggers should be created for the three buttons that were created on the form canvas as follows:

     Save:     begin

               commit_form;

          end;

     Clear:    begin

               clear_form;

          end;

     Exit:     begin

               do_key ('EXIT_FORM');

          end;

The first two triggers use standard built-in procedures, while the third uses the DO_KEY built-in procedure to simulate pressing the Exit key.

This is generally a good practice to use when there is more than one way to perform the same function. Any special logic that needs to be performed prior to exiting the form can be coded in a KEY-EXIT trigger to ensure consistency. Additionally, a trigger can be written so that it calls a user defined program unit. For example, if the customer maintenance form changes the credit limit for a customer, an acceptance letter should be sent to the customer. The POST-COMMIT trigger should be written as follows:

begin

        if :customers.cust_credit_limit > 0 and :customers.old_credit is null then

           print_confirmation_letter (:customers.cust_no);

        end if;

     end;

The print_confirmation_letter procedure would then be created as a program unit in the form. The actual logic for this procedure will be discussed in Chapter 36 in the section describing integration of Oracle Forms and Oracle Reports.

Using Record Groups and Lists of Values for Data Validation

A record group is an internal structure that is analogous to a table in memory. Record groups contain columns and rows with data based on a structure defined by a query or column definition. Usages of record groups include parameters, structured arrays, and validation entities. This last usage of record groups will be demonstrated in the Item Price Maintenance form shown in Figure 32.15.

Figure 32.15. Item Price Maintenance form.

To build this form, start with a default block for the items table using the form style layout. Next, change the Displayed Canvas to <Null> and the Displayed property to False for the ITEM_PL_ID, ITEM_PC_ID, ITEM_PROD_NO, ITEM_CP_NO, and ITEM_SIZE_CODE items. These items will be hidden from view and will be updated by the List of Values validations that will be constructed for this form. Also, make the ITEM_NO field non-updatable and non-navigable.

To make this form more user-friendly, the translations for the code fields will be displayed as the input fields in the form. Using the Field tool, create five new fields as PL_NAME, PC_NAME, PROD_NAME, CP_NAME, and SIZE_DESC. The properties for these fields are shown in Table 32.3.

Table 32.3. Item maintenance description fields.

Property

Value

These fields will need to be populated with data whenever an ITEMS record is queried. To do this, create the POST-QUERY trigger on the ITEMS block as follows:

     begin

        select pl.pl_name, pc.pc_name, prod.prod_name, cp.cp_name, s.size_desc

           into :items.pl_name, :items.pc_name, :items.prod_name, :items.cp_name,

                  :items.size_desc

           from product_lines pl, product_classes pc, products prod,

                   color_patterns cp, sizes s

         where pl.pl_id = pc.pc_pl_id

             and pc.pc_pl_id = prod.prod_pl_id

             and pc.pc_id = prod.prod_pc_id

             and prod.prod_pl_id = :items.item_pl_id

             and prod.prod_pc_id = :items.item_pc_id

             and prod.prod_no = :items.item_prod_no

             and cp.cp_no = :items.item_cp_no

             and s.size_code = :items.item_size_code;

     exception

        when NO_DATA_FOUND then

           message ('Database Integrity Error. Contact your DBA.');

           bell;

           raise FORM_TRIGGER_FAILURE;

     end;

Now the lists of values should be defined for each of the fields. To create a list of values, select LOVs in the Object Navigator and click the Add Object button. The new LOV dialog box will appear as shown in Figure 32.16. In the Query Text box, type in the query against the PRODUCT_LINES table as shown and click the OK button when finished. This will create a new record group and associate it with the new LOV. (Note that a list of values may also be created based on an existing record group.)

Figure 32.16. Creating a new list of values.

Now, select the new LOV and its property sheet. Change the name of the LOV to PRODLINE_LOV and select the Column Mapping property in the property sheet. A button will appear in the Value Edit box with the label More. Click this button to display the column mapping dialog box as shown in Figure 32.17. The column names from the query will be displayed in a table with the characteristics for the column shown below the table. To hide the ID column from the display, set the display width equal to 0. Select the PL_NAME column and set its display width to 150 and change the column title to Product Lines. Click OK to complete the column mapping. Now attach this LOV to the PL_NAME field in the ITEMS block by selecting its property sheet and scrolling towards the bottom to the Miscellaneous Properties section. Change the LOV property to PRODLINE_LOV and set the LOV X Position and LOV Y Position to 100 and 50, respectively. Finally, set the LOV For Validation property to True. This will cause the form to make sure that the value entered is valid without having to write a validation trigger. (To ensure data integrity, a WHEN-VALIDATE-ITEM trigger may be written for this field to "null out" the Product Class and Product Name fields whenever the Product Line is changed). Create a List of Values for each of the remaining non database fields. (Use the POST-QUERY trigger to determine the columns and tables for the mapping.)

Figure 32.17. Column mapping for a list of values.

Finally, to complete the form, create a PRE-INSERT trigger on the items block to select the next ITEM_SEQ value from the sequence generator as the value for a new ITEM_NO. The form should then be saved and generated to test this concept. A couple features to note are that the list can be activated using the List Values key (F9 in most standard IBM PC configurations. To see a list of defined keys, select Help | Show Keys from the menu) and that the validation feature allows the user to type only part of the field name to narrow the list. For example, type T into the Product Line field and then hit the tab key. The complete name, Terminal Tackle, will be filled in.

Using Relationships to Link Separate Blocks in a Form

Thus far, all of the examples discussed here have used only a single block for data. The key feature of Oracle (or other relational databases) is that the tables are related to each other by key fields. An example of the use of related tables is shown in Figure 32.18 in the Order Entry Form.

Figure 32.18. Order entry form.

To construct this form, first create a default block for the Orders table using the form layout style. Create non-database fields for customer name, address, and city using the Display Field tool. (A display field is used to display data but does not need to provide input capability). Also, create a list of values for the customer number field and define the order number and order date fields as non-navigable. (These fields will be populated in a pre-insert trigger.) A post-query trigger should be written to populate the customer information.

Now, create a second block for the order_items table using a vertical tabular style that will display five rows of data. Modify the properties for the OI_ITEM_NO field so that it is not displayed and is assigned to the <Null> canvas. The only field that will remain on the canvas will be OI_QTY. Create non-database fields in this block for CATALOG_NO, ITEM_DESC, LIST_PRICE, ITEM_TOTAL using the Display Field tool. Also, create a numeric, non-displayed field ITEM_PRICE on the null canvas. Create a POST-QUERY trigger for the ORDER_ITEMS block as follows:

begin

        select i.item_pl_id||i.item_pc_id||'-'||

                  ltrim (to_char (i.item_prod_no, '099999'), ' ')||'-'||

                  ltrim (to_char (i.item_cp_no, '09'), ' ')||'-'||

                  i.item_size_code,

                  p.prod_name||decode (cp.cp_name, 'N/A', ' ',' '||cp.cp_name||' ')||

                  s.size_desc,

                  i.item_price

           into :order_items.catalog_no, :order_items.item_desc, :order_items.item_price

           from items i, products p, color_patterns cp, sizes s

         where items.item_no = :order_items.oi_item_no

             and p.prod_pl_id = i.item_pl_id

             and p.prod_pc_id = i.item_pc_id

             and p.prod_no = i.item_prod_no

             and cp.cp_no = i.item_cp_no

             and s.size_code = i.item_size_code;

        :order_items.list_price := ltrim(to_char(:order_items.item_price, '990.00'), ' ');

        :order_items.item_total := ltrim ( to_char

                              ((:order_items.oi_qty * :order_items.item_price), '990.00'), ' ');

     end;

To complete this block, create a list of values for the catalog number field with a WHEN-VALIDATE-ITEM trigger to display the list price and item total fields.

Finally, to complete this form, select the Relationships group under the Orders block. Click the Add Object button to display the Relation dialog box as shown in Figure 32.19. Modify the relation name to order_item_rel and define the detail block as ORDER_ITEMS. Next, define the logic that should be followed if a master record is deleted. In this case, select a cascading delete. (All detail records will be deleted if the associated master record is deleted.) The block coordination should be defined so that the detail query is immediate (Deferred is off), and the user should not be able to navigate to the detail block unless a record exists in the master block.

Figure 32.19. Creating block relationships in a form.

Mouse Events, Timers, and Other Advanced Widgets

The standard Windows interface uses various graphical controls and other objects to control the operation of application components. Oracle Forms provides access to many of these features through the use of mouse triggers, timers, and VBX controls. Additionally, messaging in most Windows software is through an object called an alert box that has been implemented in Oracle Forms.

Working with the Mouse

The mouse pointer is the primary user-input device for navigation and selection in most Windows applications. Triggers have been provided in Oracle Forms to detect and act on various mouse activities.

Oracle Forms 4.5 utilizes the mouse for navigation and command input. Additionally, the mouse can be used to trigger specific events. An event can be triggered when the mouse passes over an item on the screen (WHEN-MOUSE-ENTER) or when it leaves the item (WHEN-MOUSE-LEAVE). A third mouse status event can occur if the mouse moves within an item (WHEN-MOUSE-MOVE).

Additional triggers have been added for mouse button activities:

Table 32.4. Mouse triggers.

Trigger Name

Event Description

When these activities occur, several system variables exist to retrieve status information for the mouse. These variables are:

Table 32.5. Mouse system variables.

Variable

Value

The sample form shown in Figure 32.20 can be constructed to test and observe the operations of the mouse triggers and variables. To construct this form, create a block, b1, that is not associated with a table. In the Layout Editor, create four fields for TRIGGER_NAME, BUTTON_NUMBER, SHIFT_STATE, and MOUSE_ITEM1, and position these fields with the appropriate caption as shown in the figure. Set the Default value property for the MOUSE_ITEM1 field as WILL TURN RED ON MOUSE ENTRY. Also, create a button object, DRAG_BUTTON, on the canvas with a Label property of Drag This Button.

Figure 32.20. Mouse observation form.

Next, select the Visual Attributes group in the Object Navigator and click the Add Objects button. In the property sheet for this object, set the font to Arial, size 8, and weight bold. Define the foreground color as BLACK and set the background to WHITE. Name this object BLACK_ON_WHITE. Create a second visual attribute, WHITE_ON_RED, with a white foreground and red background. These visual attributes will be used to define the display colors of the MOUSE_ITEM1 field using a WHEN-MOUSE-ENTER trigger as follows:

     begin

        :b1.trigger_name := 'MOUSE ENTER';

        set_item_property ('B1.MOUSE_ITEM1', VISUAL_ATTRIBUTE,

                                        'WHITE_ON_RED');

     end;

Similarly create a WHEN-MOUSE-LEAVE trigger to use the BLACK_ON_WHITE attribute. Now, create WHEN-MOUSE-DOWN, WHEN-MOUSE-UP, WHEN-MOUSE-CLICK, and WHEN-MOUSE-DOUBLECLICK triggers at the form level to display the status of the mouse whenever a trigger event occurs.

     begin  -- WHEN-MOUSE-DOWN trigger

        :b1.trigger_name := 'MOUSE DOWN';

        :b1.button_number := :system.mouse_button_pressed;

        :b1.shift_state := :system.mouse_button_shift_state;

     end;

The default installation of Oracle Forms includes several libraries and sample programs that can be used in your Forms development. One of these libraries, DRAG.PLL, provides functions that can be used for drag-and-drop functionality in Oracle Forms. To use this library, select the Attached Libraries group in the form and click the Add Object button. Select the DRAG.PLL file to attach to the form.

To implement drag-and-drop in this form, create two triggers on the DRAG BUTTON item as follows:

     begin -- WHEN-MOUSE-DOWN trigger

        mouse.click;

     end;

     begin -- WHEN-MOUSE-MOVE trigger

        if :system.mouse_button_pressed = 1 then

           mouse.move;

        end if;

     end;

These triggers that reference procedures in the mouse package in the DRAG.PLL library are all that is needed to implement drag operations in a form. A third trigger should be created for the object to define the logic associated with the drop operation (WHEN-MOUSE-UP trigger).

This completes the design of the mouse control form. Run the form to observe how it operates. A few important points should be noted at this time. First, observe the operation of passing the cursor over the MOUSE_ITEM1 field. The color of the field will change and the name of the trigger will appear in the appropriate field. Now, click anywhere on the canvas. Three triggers will actually fire with what appeared to be a single action. The WHEN-MOUSE-DOWN and WHEN-MOUSE-UP triggers fired before the WHEN-MOUSE-CLICK trigger. A double-click event will fire all of these triggers before firing the WHEN-MOUSE-DOUBLECLICK trigger. Therefore when working with the mouse, care should be taken when defining multiple triggers to prevent unwanted logic to be executed.

Working with Alerts

Alerts are devices that can be included in a form to provide the user with information that requires a response. An alert can be one of three styles: Stop (usually fatal errors), Caution (warning messages) and Note (informational). Depending on the style chosen, a different icon will appear in the alert box. Additionally, the programmer may define up to three labeled buttons to determine the user response. The default setting is a two-button alert box with the captions OK and Cancel. To display the alert, a built-in function has been provided using the following syntax:

     button_no := SHOW_ALERT (alert_name);

where button_no is defined as a numeric PL/SQL variable. Using the SET_ALERT_PROPERTY built-in, the ALERT_MESSAGE_TEXT property can be dynamically changed at runtime. Thus, using the standard trigger, ON-MESSAGE, an alert box can be created that will present all messages to the user in an alert box rather than on the status line, which may sometimes be missed by a user. An ON-MESSAGE that uses the MSG_ALERT dialog box (STOP, 1 button labeled OK) can be written as follows:

     declare

        msgtext     VARCHAR(80) := message_text;

        bno            number;

     begin

        set_alert_property ('MSG_ALERT', ALERT_MESSAGE_TEXT, msgtext);

        bno := show_alert ('MSG_ALERT');

     end;

Thus, whenever the message built-in is used, the message will be displayed as shown in Figure 32.21. The form that contains this alert will be described in the next section.

Figure 32.21. Alert message.

Using Timers for Event Control

Timers may be used in Oracle Forms to trigger events that are dependent on a specific time interval. These timers may be iterative (repeating) or one-time only. Examples of iterative timers are a report queue manager that looks for requests every 15 seconds or a database status form that "refreshes" the screen every two minutes. Uses for a one-time only trigger may be as a delay timer for button help or as a timeout trigger. To create a timer, the following command would be issued:

     TIMER_ID := CREATE_TIMER (timer_name, interval, REPEAT|NO REPEAT);

where TIMER_ID is a PL/SQL variable of type TIMER, TIMER_NAME is the name given to the timer by the programmer, and INTERVAL is the duration of the timer in milliseconds.

Oracle Forms supports multiple timers; however, only one WHEN_TIMER_EXPIRED trigger may be included at the form level. To determine which timer has expired, the trigger should use the GET_APPLICATION_PROPERTY (TIMER_NAME) built-in function. Then by checking against the various timer names, the appropriate program sequence may be executed. The SET_TIMER (same syntax as the CREATE_TIMER built-in) built-in may be used to restart an existing timer or to change its interval or repeat parameters. Finally, the DELETE_TIMER built-in may be used to remove a timer.

The example shown in Figure 32.22 shows how to implement multiple timers in a form using an iconic button bar. The WHEN_NEW_FORM_INSTANCE trigger creates two triggers that will be used in the form and the WHEN_TIMER_EXPIRED trigger executes the logic necessary when a timer expires. The first trigger is used to create an animated button in a form button bar by toggling the icon file used based on a time interval. The second timer is used to validate that the user enters a valid name within 30 seconds or the form will terminate. Finally, the WHEN_MOUSE_ENTER and WHEN_MOUSE_LEAVE triggers have been set up to create a timer that will display button help after the mouse has been "resting" on a button for at least one half second.

Figure 32.22. Timer demo form.

First, create a canvas called DESKTOP and a non-database block called control. The Desktop and associated window should be defined as 300 points wide by 200 high. Create the USER_NAME field as shown on the desktop with the appropriate valid condition. Now, create an alert, called TIMEOUT_ALERT, as an informational alert with one button. The message text for this alert should be

R E M I N D E R

     This form will terminate unless a valid user name

     is entered within 30 seconds after startup.

This alert will be displayed whenever the Show Note button is pressed.

Now, to create the iconic button bar, create a second canvas called BUTTON_BAR. The Canvas Type property for this canvas should be Horizontal Button Bar and it should be 300 points wide by 30 points high. Now create a button for the exit function. Properties for this button are shown below in Table 32.5.

Table 32.5. Button bar iconic button properties.

Property

Value

Additionally create a second button, SHOW_NOTE, adjacent to the EXIT_BUTTON that will use the lighton iconic file. (Note the icon file will change to 'blink' the light at runtime.) Now, the button bar must be defined as such to the form. To do this, change the Horiz. MDI toolbar to point to the BUTTON_BAR canvas. This will cause the button bar to appear outside the frame of the form window when the form is executed.

Create triggers that will execute the proper commands when the button is pressed. The trigger for the EXIT_BUTTON item should be DO_KEY ('EXIT_FORM') and the following WHEN-BUTTON-PRESSED trigger should be created for the SHOW_NOTE button:

     declare

        bno      NUMBER;

     begin

        bno := show_alert ('TIMEOUT_ALERT');

     --

     -- Note additional logic may be placed here based on the button pressed

     -- by the user.

     --

     end;

Now, the timer triggers may be added to the form. First, the timeout and blink timers are set up for the form in the WHEN-NEW-FORM-INSTANCE trigger. (This trigger replaces the KEY-STARTUP trigger in Forms 3.0.) This trigger is coded as follows:

     declare

        timeout_id          TIMER;

        blink_id               TIMER;

     begin

        timeout_id := CREATE_TIMER ('TIMEOUT', 30000, NO_REPEAT);

        blink_id := CREATE_TIMER ('BLINK', 500, REPEAT);

     end;

Additionally, triggers need to be added to provide button help as needed. This help text, which is a standard in many Windows applications, displays the value that was entered for the button Label directly below the iconic button. To add this functionality, attach the HINT.PLL library to the form and create a WHEN-MOUSE-ENTER and WHEN-MOUSE-LEAVE trigger for the form as follows:

     begin -- WHEN-MOUSE-ENTER trigger

        HINT.ShowButtonHelp;

     end;

     begin -- WHEN-MOUSE-LEAVE trigger

        HINT.HideButtonHelp;

     end;

If the user enters a valid name in the user name field, the timeout timer should be canceled. To do this, create a WHEN-VALIDATE-ITEM trigger for the USER_NAME field.

     begin -- WHEN-VALIDATE-ITEM trigger

        if :control.user_name is not null then -- other validation logic may be needed.

           delete_timer ('TIMEOUT');

        end if;

     end;

To complete this form, a WHEN-TIMER-EXPIRED trigger must be written for all timers in the form. This trigger, shown forthwith, determines the timer that caused the trigger that fired and processes the logic associated with the trigger.

     declare -- WHEN-TIMER-EXPIRED trigger

        which_timer     VARCHAR2(50);

     begin

        which_timer := get_application_property (TIMER_NAME);

        if which_timer := 'BLINK' then

           :control.message_switch := mod (:control.message_switch +1, 2);

           if :control.message_switch = 0 then

              set_item_property ('CONTROL.SHOW_NOTE', ICON_FILE, 'lightoff');

           else

              set_item_property ('CONTROL.SHOW_NOTE', ICON_FILE, 'lighton');

           end if;

        elsif which_timer = 'TIMEOUT' then

           message ('Timeout Occurred.  Form Canceled.');

           do_key ('EXIT_FORM');

        else

           HINT.ShowButtonHelpHandler;

        end if;

     end;

A couple of important points should be noted when working with timers:

1. Only one timer can be handled by the WHEN-TIMER-EXPIRED trigger at a time. If a second timer expires while this trigger is handling the first, it will be placed on the stack until the trigger is completed.
   
   
2. The WHEN-TIMER-EXPIRED trigger will fire during transaction processing, navigation, and so on. If a second form is called by the form containing the trigger, the timer will be deferred until the user returns to the calling form.
   
   
3. A repeating trigger will not repeat until it is taken off the queue. In other words, the interval does not start up again until the first iteration is handled.
   
   
4. Any existing timers are deleted when the form is exited. If any timed activity is pending or on the queue, it will not complete if the form is exited.
   
   
5. Finally, DO NOT use timers where precise timing is essential. Because the above conditions can delay the actual execution time of the trigger logic, the Oracle timer can not be used for industrial fail-safe operations.
   
   

Implementing VBX Controls

VBX controls were originally developed as user interface elements for Microsoft Visual Basic programs. As the Visual Basic environment became accepted as a powerful business-applications development environment, interfaces to VBX controls were added to many other popular Windows program-development products. While only a few VBX controls are included with the Visual Basic software, many third party VBX controls are available for purchase. Additionally, developers may create their own VBX controls using C++ or other programming languages.

With the advent of Oracle Forms 4.5, these elements have been incorporated into the Oracle application tools. In Oracle Forms, a VBX control may be used to either provide information to an application or to display application information in some specialized way. To demonstrate the ease with which these elements may be incorporated into a form, the simple form module shown in Figure 32.23 may be constructed.

Figure 32.23. VBX demonstration form.

This form utilizes two VBX controls that are connected to a text item with triggers. The first VBX control is the Spin Control, which will increase or decrease the value in the text box by 500 units depending on whether the up arrow or down arrow is clicked with the mouse. The other VBX control is a VBX gauge control, which is defined as a horizontal bar gauge. This gauge will be filled based on the value of the text item as a percentage of the maximum value of 25,000.

To create this form, create a numeric text field, VBX_VALUE, on the form as shown. The default value for this item should be 10000. Next, create a VBX control in the Layout Designer next to the text field. This VBX control should then be attached to the VBX file for the Spin button. The properties for this object are shown in Table 32.6.

Table 32.6. Spin control properties.

Property

Value

A second VBX control should be added below the other items and attached to the gauge VBX file. The properties for this control are shown in Table 32.7.

Table 32.7 Gauge control properties.

Property

Value

Finally, triggers need to be created to link these three items. To establish the initial values, the WHEN_NEW_FORM_INSTANCE trigger should contain the following lines:

     :B_VBX.VBX_VALUE := 10000;

     :B_VBX.VBX_GAUGE := :B_VBX.VBX_VALUE;

A WHEN_CUSTOM_ITEM_EVENT trigger should then be created for the Spin Control:

     BEGIN

        if :SYSTEM.CUSTOM_ITEM_EVENT =  'SpinUp' then

           if :B_VBX.VBX_VALUE < 24501 then

              :B_VBX.VBX_VALUE := :B_VBX.VBX_VALUE + 500;

           end if;

        elsif :SYSTEM.CUSTOM_ITEM_EVENT = 'SpinDown' then

           if :B_VBX.VBX_VALUE > 499 then

              :B_VBX.VBX_VALUE := :B_VBX.VBX_VALUE - 500;

           end if;

        end if;

        :B_VBX.VBX_GAUGE := :B_VBX.VBX_VALUE;

     END;

A WHEN_VALIDATE_ITEM trigger should be written for the VBX_VALUE text item containing the following line:

     :B_VBX.VBX_GAUGE := :b_VBX.VBX_VALUE;

Finally, triggers may be written for the cursor up and down keys so that pressing either of them will trigger the corresponding Spin Up or Spin Down events:

     BEGIN  -- KEY-UP trigger

        VBX.FIRE_EVENT ('B_VBX.VBX_SPIN', 'SpinUp', NULL);

     END;

Considerations for Multiple Form Applications

Oracle Forms provides three built-in procedures that enable the user to access other forms from an original calling form. These procedures are: NEW_FORM, CALL_FORM, and OPEN_FORM.

NEW_FORM terminates execution of the original form and starts up the next form. If any changes have been made to database data, the user will be asked if he wants to commit the data. If he chooses not to commit his changes, these changes will be lost. A NEW_FORM call is typically used when the user navigates to an unrelated application module.

CALL_FORM, on the other hand, passes execution to the next form, while maintaining a call stack that will return to the calling form when the called form is exited. If changes are pending in the calling form, the called form will be executed in POST-ONLY mode. If the user tries to save changes made in the called form, the changes will be posted to the database (a rollback will lose any changes) and they will be saved when the original form is committed. Typically, CALL_FORM is used when the two forms are dependent on each other and values can be passed either in global variables or as parameters. One usage of the CALL_FORM would be to add a button to the Order Entry form (discussed in the section on relationships) that can be used to create a new customer record. The second form could then be used to create the customer record, and after the new record is inserted and posted or committed, the customer number could be returned in a global variable to be used for order entry.

Finally, OPEN_FORM is used to load a second form while maintaining the functionality of the first form. The second form, by default, becomes the active form; however the user can activate the first form by clicking within its frame. If the second form should not be made the active form, the second parameter in the procedure call can be defined as NO_ACTIVATE. Additionally, the new form will be opened in the same session as the original calling form. It is possible, however to call the second form with a separate session by defining the third parameter in the call as SESSION. This would connect the user in a second (or third, etc.) session. The advantage of having the second session open is that changes can be made within the first form and committed without affecting pending changes in the first form. An interesting application can be developed where the first form executes a query based on a timer (for example, every two minutes). The second form could then be used to maintain records on the database and the changes would show up in the original form. (This would be a crude, but effective way to pass data between two application areas.)

Techniques for Dynamic, Runtime Applications

In its simplest form, an Oracle Forms query can be defined by the default where property for the queried block. Typically, a form will contain query criteria elements in a control block and the default where property may be defined as:

     where database_table_field = :CONTROL.control_field

This would work in cases where the query is based on a single required field such as customer number. In reality, however, a query form is seldom so cut-and-dried. The customer inquiry may also need to be based on the customer name. Using the above technique, the default where property would become:

     where database_field1 = :CONTROL.input_field1 or

          (:CONTROL.input_field1 is null and

               database_field2 = :CONTOL.input_field2)

The performance of this query is poor because the Oracle optimizer will resolve both halves of the query and then merge the result. As can be seen by this basic example, as the number of query fields increases in the control block, the where clause would become more complex and the performance of the query would degrade very quickly. Ideally, the where clause should be written to reflect only the fields that contain data.

Starting with Oracle Forms 4.0, an application may modify the where clause dynamically at runtime. Thus, in the previous example, the following PRE-QUERY trigger may be written to dynamically update the query:

     declare

        qry_where     VARCHAR2(100);

     begin

        if :control.cust_no is not null then

           qry_where := 'cust_no ='||to_char (:control.cust_no);

        elsif :control.cust_name is not null then

           qry_where := 'cust_name = '''||:control.cust_name||'''';

        else

           message ('Either customer number or name must be entered.');

           raise FORM_TRIGGER_FAILURE;

        end if;

        set_block_property ('CUSTOMERS', DEFAULT_WHERE, qry_where);

     end;

While in many cases, directly building a where clause at runtime seems to be the best way to handle dynamic queries, the techniques that have been available in prior versions of SQL*Forms may be used. In order to accomplish this, a database field is set equal to a value based on how the where clause should be created. The following table will define possible entries using the field ITEM_VALUE.

Table 32.8. Implementing dynamic queries in Oracle Forms.

Field Contents

Runtime Modification

Example

Resulting Where Clause

Generally speaking, treating each column independently in the PRE-QUERY trigger will result in a more maintainable module. Given that most environments are in a constant state of change, ease of maintenance should be a determining factor.

Working with Menus

Thus far, all of the application modules that have been developed in this chapter have been completely independent of each other. While an application can be developed using iconic buttons and procedures that can be used to pass control from one form to the next, most applications are held together using menu modules. Typically, a main form is executed first and all other forms are called from the original module. As you may have noticed in the Form property sheet, each form module may define a menu to be used within the form.

To create a menu module, use the File | New | Menu menu choice in the Oracle Forms designer. A new menu module will be created in the Object Navigator. Object groups in the menu are attached libraries, menus, object groups, parameters, program units, property classes, and visual attributes. A menu is defined as a list of options that may reference other submenus or perform tasks such as commands or processing Forms functions.

The initial menu module begins by creating a menu called MAIN_MENU. To edit the main menu, double-click on the menu icon and the menu editor will appear as shown in Figure 32.24 The initial menu contains one item called <New Item>, which can be customized by double clicking on the item and defining its properties in the property sheet. Important properties to note for the item are Item Type, Command Type, and Command Text.

Figure 32.24. Menu editor.

Item types are: Plain, Check, Radio, Separator, and Magic. Most items that will be defined for navigation will be of the Plain type. A Check item is used to set a user option from the menu, and a Radio type item is used to select an option from a list of valid options. A Separator item is used to draw a horizontal line in a drop down menu. Finally, a Magic item performs a default Forms function defined by the Magic item property.

The command types are Null, Menu, PL/SQL, Plus, Form, and Macro. The Null command performs no function when the menu item is selected and a menu item will present a new submenu. A PL/SQL command type is used to execute a PL/SQL program block. Plus and Form are used to invoke SQL*Plus and Oracle Forms.

Thus, by defining a set of menus, the user will be able to create applications that can be navigated through the use of the various menu items. Figure 32.25 below shows a File Menu as it is being constructed with separators between the logical menu areas giving a similar appearance as many Windows 3.1 applications.

Figure 32.5. Sample File menu.

Libraries, Object Classes, and Visual Attributes

One of the major features of object-oriented programming environments is the concept of reusability. By creating reusable objects, standards can be enforced globally and applications can be developed at an unprecedented pace. Users become more comfortable with an application that appears and operates uniformly. Several objects have been included in Oracle Forms to provide this reusability.

As already discussed, libraries are repositories of reusable PL/SQL program units. To work with a library, create the library in the Object Navigator as you would a form. A library can attach other libraries and can contain PL/SQL program units. To use these program units in other forms, attach the library to the form and reference the procedures and functions as if they were part of the form itself. If changes are made to the library, each form that uses the library must be regenerated to reflect the change.

Visual attributes, on the other hand, are defined as part of the form itself. A visual attribute defines the font characteristics and the colors for the object. A special visual attribute called Default exists for every form based on the value of the FORMS45_DEFAULTFONT parameter in the oracle.ini file in your system. Visual attributes can be designed to define meaning for the item. For example, a financial application may show negative values in red or special characteristics may be needed to indicate errors or statistical extremes. Another valuable usage of a visual attribute is to indicate the current record selected. The current-record attribute is available at either the block or form level and is often used in multirow applications. Finally, the visual attribute for an instance of an item can be changed dynamically at runtime using the DISPLAY_ITEM built-in procedure.

The last feature that provides reusability within a form is a property class. A property class takes the represented by the visual attribute and utilizes it further by defining all of the properties for a class of objects. This allows the developer to define a set of valid property classes and to use these properties throughout the application without having to define each individual property for every object.

Quality Considerations for Oracle Forms

Oracle Forms can handle a vast array of database processing functions. As with any powerful tool, there are many ways to accomplish the same task. It is the job of the developer to create applications that deliver the highest quality return for the lowest overall cost. Quality in an Oracle system can be classified according to several key attributes:

• Reliability: The system must deliver accurate and dependable results.
  
  
• Performance: The system must deliver the results quickly.
  
  
• Features: The system must deliver the functionality needed.
  
  
• Durability: The system must sustain growth.
  
  
• Serviceability: The system must be easily modified.
  
  
• Conformance: The system must adhere to standards.
  
  
• Aesthetics: The system must look good doing all of the above.
  
  

Oracle Forms provides many tools to ensure the quality of an Oracle application. Triggers provide the constructs necessary to ensure the reliability of a form in adhering to business rules as they have been established. By coupling these concepts with database triggers and procedures, the system development process can enforce conformance to all of the business rules. In addition, program unit libraries and reference forms with defined object classes and visual attributes can assist the developer in delivering a reliable system that conforms to the standard presentation format that has been established for the organization. These components also make a system easier to modify to reflect changes in rules or newly desired interfaces.

Such modifications are inevitable in practically every Oracle database application, and if object-oriented principals are used from the beginning, changes will be easier to make in the future.

The final concept that is absolutely key in the development of Oracle applications is performance. Unfortunately, in many cases, performance is an afterthought in application development. Many systems actually end up in production without any performance tuning. At best, most applications have received only minimal tuning effort. This is not completely the fault of the developer, although the developer should be most concerned with the performance of the system. After all, whenever a system performs poorly, the finger of blame will usually point to the developer.

What can be done by the developer to ensure optimum performance? The answer, in a word, is testing. Each SQL statement executed in the form should be checked for optimized code. To see what SQL statements are being run for a form, run the form with the STATISTICS mode on. This will create a trace file in the Oracle Home directory. (Your DBA should be able to help you find this directory.) The trace file should then be translated using the TKPROF utility (Oracle's performance tuning utility) as follows:

     TKPROF tracefile listfile EXPLAIN=username/password

This utility will describe the access path for every SQL statement executed in the form. Look at the execution plans to make sure that every table access uses an index where desired and that full table scans are minimized. Generally, when using multiple table views in a query, the result should be minimized as quickly as possible. Therefore, make sure that the indexes that return the fewest result rows are used earliest. Second, a full table scan is not always a bad thing in a query, especially when most of the blocks in a table must be accessed anyway. Tuning individual statements will come with practice, and many times even a seasoned veteran can find the optimum query through trial and error.

Besides making sure that each SQL statement is efficient, there are several other "rules" that the developer can follow when building a form, especially in a client/server environment. Some of these include:

• Minimize the number of queries. A POST-QUERY trigger will be called for every row returned by the initial query. Try to create a view that contains all the information in the first query.
  
  
• Combine unrelated queries if possible. Several queries that all return one row using a unique index can be combined into a single query. This saves round-trips over the network.
  
  
• Use database triggers and procedures where possible. These constructs ensure that all maintenance to the data uses the same business rules. Additionally, communications to the database will be minimized.
  
  
• Finally, before rolling out to production, stress test the application with a large volume of data with many users. This testing will shake out bottlenecks and indicate where locking strategies may need to be revised.
  
  

Summary

The material presented in this chapter has defined the primary building blocks for creating Oracle Forms applications that access data in an Oracle database. Items are built into form blocks, which appear on a canvas in defined windows. Triggers and program units combine to provide a robust development system that can be customized to the specific needs of the end users. Properties, lists of values, alerts, and the various graphical objects further enhance the ability of the developer to construct useful and powerful database applications.

Unfortunately, because of the limitations of defining the entire tool in a single chapter, many of the topics were not explained in as much detail as is needed to make you an expert developer. What I hope to have provided you is a set of tools and the basic knowledge to use them. You can think of yourself now as the equivalent of an apprentice carpenter fresh out of trade school. Only with experience using the tools can the apprentice hone his skills until he can be considered a master craftsman. Likewise, the only way to learn how to use a tool set as powerful as Oracle Forms is to use the tool and try new techniques until you too are an expert.