Data blocks that correspond to a specific database table automatically support querying, updating, inserting, and deleting records, with no additional coding required. By setting the properties of the block and its items, you can control, modify, and even prevent queries as desired.
A form can be placed in Enter Query mode by an operator pressing [Enter Query], or by executing the ENTER_QUERY Built-in procedure. Oracle Forms remains in Enter Query mode until the query is executed by the EXECUTE_QUERY operation, or until the operator presses [Cancel].
While the form is in Enter Query mode, operators set the values of items in the current block to specify criteria for the query. Operators can use pattern matching, relational operators, and the QUERY WHERE dialog box to define the example record.
The ENTER_QUERY and EXECUTE_QUERY operations, whether initiated by the operator or programmatically, always occur at the block level. Once a form is in Enter Query mode, Oracle Forms does not allow the operator or the application to navigate to a different block until the query is executed or the operator cancels Enter Query mode.
Set the Query Allowed block property to specify whether a block is queryable. To make a "query-only" block, set Query Allowed to Yes and Delete Allowed, Insert Allowed, and Update Allowed to No.
There is a default SELECT statement associated with each data block. At runtime, Oracle Forms executes this SELECT statement when a query is made against the block, either programmatically or by the operator.
The syntax for a block's default SELECT statement is derived from the names of the data items in the block. A data item is an item in a data block that has its Database Block property set to Yes. The name of a data item must match the name of a column in the block's base table. For example, in the Sample form, when operators execute a query in the ORD_BLK block, Oracle Forms uses the following default query:
SELECT id, customer_id, Date_ordered, payment_type, order_filled,
sales_rep_id FROM S_ORD
The Customer_Name and Sales_Rep_Name items are also in the ORD_BLK block, but they are control items whose values are derived from columns in different tables. Because these items have the Database Block property set to No, Oracle Forms does not include them in the default SELECT statement.
The default query returns a value from each database column for which there is a corresponding item in the block. The query is unrestricted by default, and retrieves all rows from the base table in no particular order.
Operators can restrict the rows retrieved by a query by entering values in data items. Oracle Forms then uses these values to construct the WHERE clause for the default SELECT statement.
For example, if the operator navigates to the ORD_BLK block, presses [Enter Query], types 55 in the ID text item, and then executes the query, Oracle Forms issues the following SELECT statement:
SELECT id, customer_id, Date_ordered, payment_type, order_filled,
sales_rep_id FROM S_ORD WHERE id = '55'
You can also specify the WHERE and ORDER BY clauses by setting the WHERE and ORDER BY block properties, either at design time or at runtime. For example, if you had set the ORDER BY Clause property for the ORD_BLK block to "id DESC, " the SELECT statement from the previous example would have been issued as follows:
SELECT id, customer_id, Date_ordered, payment_type, order_filled,
sales_rep_id FROM S_ORD WHERE id = '55' ORDER BY id DESC
When Oracle Forms executes a query, it identifies the rows in the database that match the query criteria, and then fetches the number of rows required by the block. You can control the number of records that Oracle Forms fetches at one time by setting the Maximum Records Fetched block property.
About defining a block's WHERE and ORDER BY clauses
About defining items for Enter Query mode
About defining check boxes, radio groups, and List items for querying
About executing a query programmatically
About controlling queries with Pre-Query and Post-Query triggers