Page 259
The form can be placed into the query mode in three ways: clicking the Query button, pressing the F7 function key, and selecting the Query/Enter option from the menu. The query can be executed in three ways: clicking the Query button when the form is in the Query mode, pressing the F8 function key, and selecting the Query/Execute option from the menu located at the top of the form.
Query ValuesThe same types of values or expressions used in the where clause of a select statement (discussed in Chapter 3) can be placed into the item. This consists of entering a number in a numeric field or a character string in an alphanumeric item. Oracle Forms return values that match the entered value. You may also use the like symbols `%' and `_'. For instance, placing the characters `WOOD%' in the first name field and executing the query will return the records for `WOODROW WILSON.'
NOTE |
Alphanumeric or character values do not need to be enclosed by single quotes. When creating the form, each item has a defined data format. When you enter a value into the item Oracle makes the entered value the same as the defined data format. Thus, entering a value of 1 in a character defined item makes the value a character value of 1. This eliminates the need for single quotations, since Oracle knows what the data type is.n |
Relational operators can also be placed in the fields. Table 11.3 illustrates examples of various expressions that contain relational operators.
Table 11.3 Examples of Various Operators that Can Be Used in Item Values
Operator | Example | Description |
= | =WILSON | Equal to Wilson |
<> != | <>WILSON or !=WILSON | Not equal to Wilson |
>= | >= WILSON | Greater than or equal to Wilson |
<= | <=WILSON | Less than or equal to Wilson |
< | <WILSON | Less than Wilson |
> | >WILSON | Greater than Wilson |
# | #BETWEEN 1 and 3 | Between 1 and 3 |
The # operator is used when the queried value consists of an evaluation function. The between and in evaluation functions are examples this type of function. The # operator must precede the expression.
In Figure 11.9, the query is to retrieve records from the Employee table where the value in the Wages field is null. This requires the keywords `#is null' to be placed in the Wages item.
Page 260
FIG. 11.9
Querying a record that
contains a null value in
the wages column.
TIP |
When an item is first defined, it defaults to the length of the corresponding table field. This may be a shorter length than the amount needed for an expression using the # key symbol. Each field has a query length property that will be discussed later in Chapter 14. This property can be set to a larger value enabling the item to hold a larger expression. |
When you perform a query, sometimes more than one record is retrieved by Oracle. In fact, if you press the F8 or Execute function key, Oracle returns all of the rows of the table as the results of the query. When you are looking at a one-record form or block, it may not seem like multiple records were retrieved because the form can show only one record at a time. You can navigate to other records in the set by pressing the next record button or pressing the down-arrow on the keyboard. You can return to the previously displayed records by pressing the previous record button or the up-arrow. When you have navigated past the last record in the set, the form will change to the input mode (all fields are blank) and a message will appear on the status line indicating the last record was displayed. When navigating to the previous record, the form will issue a message that you are at the first record when you try to navigate past it.
You might try this procedure using the EMPLOYEE.FMX form file contained on the CD. Use a value of "WEL" as the value in the department item. This value should retrieve a set of five records.
Page 261
A number of common procedures are executed on a form. You have already seen how to query the database and navigate through the records. Other common procedures consist of placing the form in the input mode, canceling a query, saving the changes, and displaying errors.
The form can be placed in the input or create record mode in several ways. The first method is to navigate past the last record in the set of queried records. A second method is to press the insert record function key. This causes the record that contains the input focus to clear of existing value, placing the record in the input mode. A third method is to cancel the query mode.
When a master block is placed in the input mode, special code exists to place the related detail block in the input mode as well. When the input focus is in a detail block, only the record that contains the input focus will be placed in the input mode. Figure 11.10 illustrates placing a detail block record in the input mode. Notice row three of the Tools block is blank. This was caused by pressing the insert record or F6 function key while the input focus was on row two.
FIG. 11.10
The Create Record
function key is used to
place a Tools Detail
Block Record in the
Input mode.
Cancelling a QueryQueries are cancelled by attempting to exit from the form. This may be done in several ways. The first is to press the exit or Ctrl + Q function key. The second is to press the application close button located at the top left of the window. A third is to press the Action/Exit menu option. When the user cancels the query, the Enter a query; . message disappears, a FRM-40353: Query cancelled message appears, and the block is placed in the input mode. Figure 11.11 illustrates the Employee Form after the query has been cancelled.
When the query does not return any records, a message appears stating FRM-40301: Query caused no records to be retrieved. Re-enter. The query values will remain on the screen. The form is still in the query mode. New values may be entered and the query re-executed.