A script-enabled browser is required for this page to function properly.

Query Processing

Each data block has an associated data source. Action by the application or by the end user can cause Oracle Forms to issue a query to the data source. The following paragraphs describe the way Oracle Forms constructs and processes queries.

Properties of the data block and of its base table items define the SQL SELECT statement that Oracle Forms executes in the absence of additional commands. Oracle Forms sets most of these properties automatically. The application designer can set the following properties:

The application can also use Built-ins to set properties at runtime.

At runtime, end users can initiate queries by a two-step process:

  1. Place the form into Enter Query mode and enter query-by-example conditions in fields. This creates an example record. By typing a colon in a field, the end user can cause Oracle Forms to open the Query Where dialog box, allowing the end user to specify conditions that are too complex to specify by entering values into fields. This can include specifying an ORDER BY clause.
  2. Direct Oracle Forms to execute the query using the example record.

The application can modify the example record that the end user creates, or it can create an example record without end user action. Oracle Forms combines the example record with the previously established block properties to produce the query it issues to the database.

Oracle Forms performs the following steps to create a SELECT statement to issue to the database:

  1. Build a SELECT statement using column names that correspond to the base table items in the block.
  2. Add a WHERE clause built by ANDing the WHERE Clause block property with additional WHERE conditions derived from the example record. For example, if the end user types the value 10 into the DEPTNO field, Oracle Forms adds the additional condition DEPTNO=10 to the WHERE clause. If any of the base table items in the block has a non-null value for its Copy Value from Item property, Oracle Forms incorporates this into the WHERE clause.
  3. Add an ORDER BY clause by taking the ORDER BY clause from the example record. If the example record does not contain an ORDER BY clause, use the contents of the ORDER BY Clause block property.

Oracle Forms reinitializes the example record whenever a program statement, menu selection, or function key causes Oracle Forms to execute any of the ENTER_QUERY, EXECUTE_QUERY, or COUNT_QUERY Built-ins or to enter one of the corresponding interactive modes.

Designers can assign values to items in a PRE-QUERY trigger. Note that changes made to the SELECT statement by a PRE-QUERY trigger override values entered by an end user in Enter Query mode. This functionality allows the designer to screen illegal queries.

Query Where dialog box

End users can invoke the Query Where dialog box by entering a colon in a field in Enter Query mode. This allows them to supply values for the WHERE clause by entering query conditions, using pattern matching and relational operators.

Processing in Enter Query mode

When a form is in Enter Query mode, Oracle Forms modifies its processing rules as follows:

Block Menu

Next Block

Clear Form/Rollback

Next Record

Commit

Next Set of Records

Down

Previous Block

Duplicate Item

Previous Record

Duplicate Record

Scroll Down

Insert Record

Scroll Up

Lock Record

Up