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.
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 (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 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 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.
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.
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 (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 (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.
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.
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 (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.
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 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 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.
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.
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.
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.
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.
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 various windows in Oracle Forms may be maximized within the Designer window. However, with practice, using multiple windows simultaneously (as shown in Figure 32.1) can accelerate the programming phase of the
module. As the developer moves around the Object Navigator, the appropriate property sheet will be displayed, and by selecting multiple items in the navigator, the common properties will be shown. This is a matter of personal preference, and you may find
that having multiple windows is too confusing. I do, however, encourage you to try before deciding what you like best.
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.
Button |
Usage |
|
Select |
Selects object(s) on the canvas. |
|
Magnify |
Zooms in/out on the canvas. Click within the canvas to zoom in. Click while holding the shift key to zoom out. The current magnification level is shown in the lower-left area of the Layout Editor window. |
|
Rectangle |
Draws a rectangle on the canvas. |
|
Ellipse |
Draws a circle or ellipse. |
|
Polygon |
Draws a multiple sided object. |
|
Rounded Rectangle |
Draws a rectangle with rounded corners. |
|
Text |
Adds boilerplate text to the canvas. |
|
Check Box |
Creates a check box item. |
|
Text Item |
Creates a text item field. |
|
Oracle Graphics |
Inserts an Oracle Graphics chart item. |
|
VBX |
Creates a VBX control item. |
|
List Item |
Creates a drop down list item. |
|
Rotate |
Rotates the selected object. |
|
Reshape |
Reshapes the selected object. |
|
Line |
Draws a line. |
|
Arc |
Draws a curved line. |
|
Polyline |
Draws a series of connected lines. |
|
Freehand |
Draws in freehand mode. |
|
Button |
Creates a button item. |
|
Radio Button |
Creates a radio button item. |
|
Image Item |
Attaches an image item to the canvas. |
|
OLE |
Creates an OLE container item. |
|
Display Item |
Creates an item for display purposes only. |
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:
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.)
Many of the earlier versions of Oracle Forms were notorious for producing General Protection Faults, which usually resulted in work being lost. Thus, it became a common practice to use these checkboxes practically
universally. Unfortunately, unless a backup of a module is saved, changes can wipe out prior versions of a form if these options are used.
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.
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.
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.
DB Column |
Label |
Width |
|
WH_CODE |
Code |
35 |
|
WH_NAME |
Warehouse |
150 |
|
WH_OPEN_DATE |
Opened |
60 |
|
WH_CLOSE_DATE |
Closed |
60 |
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.
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.
Sometimes the intelligent defaulting feature of Oracle Forms causes objects to appear differently than desired, especially regarding the fill and border-line visual attributes for the object. These can be
corrected using the attribute palette buttons below the current visual-attribute display in the Layout Editor. Also, the Arrange menu provides options to move objects in front of or behind other objects.
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.
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.
Generally, lists in Oracle Forms use an algorithm to narrow a list as keys are typed. In this case after typing P and R, the list will show all PRE-x triggers and the input line will type ahead because no
other triggers exist that begin with those letters. Type an I and the PRE-INSERT trigger will be the only trigger in the list.
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.
The Exit key is defined by the keyboard mapping for your display device. On the IBM PC keyboard, this normally defaults to the Esc key, but may be modified using Oracle Terminal. To view the actual key mapping
during Oracle Forms runtime, type Cntrl-K or select show keys from the help menu in the form.
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.
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.
Property |
Value |
|
Canvas |
ITEM_CANVAS |
|
Displayed |
True |
|
Width |
200 |
|
Height |
200 |
|
Database Table Item |
False |
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;
This trigger has been written this way because in a client/server application, a single query is much more efficient than multiple independent queries. Because each of the five fields that must be loaded can be
obtained through a unique row query, they can be merged in a single query step. Another (and probably better) approach would be to create a view that retrieves these values in the initial query. A view could always be used as the base table for a block.
The exception step introduces a few new concepts regarding triggers. The message built-in is used to send a message to the user screen and the bell built-in will play an audible beep from the user's terminal. Also, the FORM_TRIGGER_FAILURE exception that
is raised prior to exiting the trigger will cause all processing to be aborted on failure.
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.
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;
Oracle Forms does not allow for a format mask on display items. For that reason, the fields should be defined as character fields and the item should be formatted using PL/SQL. Using right justification, the fields
can be displayed so that the individual digits are properly aligned.
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.
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.
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:
Trigger Name |
Event Description |
|
WHEN-MOUSE-DOWN |
Operator presses and holds the mouse button. |
|
WHEN-MOUSE-UP |
Operator releases the mouse button. |
|
WHEN-MOUSE-CLICK |
Operator quickly presses and releases button. |
|
WHEN-MOUSE-DOUBLECLICK |
Operator clicks mouse twice in succession. |
When these activities occur, several system variables exist to retrieve status information for the mouse. These variables are:
Variable |
Value |
|
MOUSE_BUTTON_PRESSED |
Returns 1 for left button; 2 for middle/right. |
|
MOUSE_BUTTON_SHIFT_STATE |
Returns <Null>, Shift+, Ctrl+, or Shift+Ctrl+ depending on key pressed. |
|
MOUSE_ITEM |
Current item where mouse cursor is located. |
|
MOUSE_CANVAS |
Current canvas where mouse cursor is located. |
|
MOUSE_X_POS |
Current x position of mouse within item. |
|
MOUSE_Y_POS |
Current y position of mouse within item. |
|
MOUSE_RECORD |
Record within block where mouse cursor is located. |
|
MOUSE_RECORD_OFFSET |
Record where mouse cursor is located relative to first displayed record. |
|
MOUSE_FORM |
Current form where mouse cursor is located. |
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.
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.
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.
Property |
Value |
|
Name |
EXIT_BUTTON |
|
Canvas |
BUTTON_BAR |
|
X Position |
0 |
|
Y Position |
0 |
|
Width |
30 |
|
Height |
30 |
|
Navigable |
False |
|
Mouse Navigable |
False |
|
Label |
Exit Form |
|
Iconic |
True |
|
Icon Name |
Exit |
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.
At any one time, only one MDI (Multiple Document Interface) button bar will appear in a Windows application. This prevents confusion when multiple documents or forms are open at the same time. Only one document
may be active at any time and the MDI button bar will show the buttons associated with the active document. This is especially useful when working with multiple forms or OLE applications.
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:
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.
Property |
Value |
|
VBX Control File |
C:\WINDOWS\SYSTEM\spin.vbx |
|
VBX Control Name |
SpinButton |
|
VBX Control Value Property |
Name |
|
Border Thickness |
1 |
|
Spin Orientation |
0 -Vertical |
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.
Property |
Value |
|
VBX Control File |
C:\WINDOWS\SYSTEM\gauge.vbx |
|
VBX Control Name |
Gauge |
|
VBX Control Value Property |
Value |
|
Max |
25000 |
|
Min |
0 |
|
Style |
0—Horizontal Bar |
|
Value |
10000 |
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;
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.)
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.
Field Contents |
Runtime Modification |
Example |
Resulting Where Clause |
|
any text value |
Checks for | ||
|
equality to | |||
|
entered value. |
SMITH |
ITEM_VALUE = 'SMITH' | |
|
begins with | |||
|
<, <=, >=, >=, >, or != | |||
|
Checks for respective | |||
|
inequalities. |
> 47 |
ITEM_VALUE > 47 | |
|
contains % or _ |
Uses pattern | ||
|
matching | |||
|
algorithm. |
%SM_TH |
ITEM_VALUE like '%SM_TH' | |
|
begins with # character |
Inserts the | ||
|
text following | |||
|
the # directly | |||
|
into the where | |||
|
clause following | |||
|
the field name | |||
|
reference. |
# between | ||
|
'01-JAN-95' | |||
|
and '31-OCT-95' |
ITEM_VALUE between '01-JAN-95' | ||
|
and '31-OCT-95' | |||
|
# in ('01', '02', '03') |
ITEM_VALUE in ('01','02', '03') | ||
|
# is not null |
ITEM_VALUE is not null | ||
|
# = 1.10 * OTHER_VALUE |
ITEM_VALUE = 1.10 * OTHER_VALUE | ||
|
# = ITEM_VALUE and | |||
|
exists (select 'x' | |||
|
from orders o where | |||
|
o.order_cust_no = | |||
|
CUSTOMERS.cust_no) |
ITEM_VALUE = ITEM_VALUE and exists | ||
|
(select 'x' from | |||
|
orders o where | |||
|
o.order_cust_no | |||
|
= CUSTOMERS.cust_no) |
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.
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.
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.
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.
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:
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:
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.
