As a continuation to the sorting capability by foreign key look-up items discussed in the preceding section, I explore here the functionality of querying by nonbase items. The DEPT_NAME item in the EMPLOYEE block ( illustrated in the preceding section) is a nonbase table, and Forms default query processing does not apply to it. A typical requirement would be to query on all employees belonging to a particular department, based on DEPT_NAME. The DEPT_ID is not as suggestive as the DEPT_NAME in identifying a department.
With the introduction of the capability to change the DEFAULT_WHERE of a base table block dynamically, querying by nonbase table items can be accomplished with a PRE-QUERY trigger containing an explicit cursor for determining the foreign key column values corresponding to the nonbase table item value.
An alternative way to order by foreign key items or query by nonbase table items involves specifying an inline SELECT in place of the base table for the block, based on a stored procedure returning a result set. See Chapter 2, "Advanced GUI Development: Developing Beyond GUI," for more details regarding this technique.
Consider the standard DEPT and EMP application. You will outline the technique of querying by DEPT_NAME that is a nonbase table item in the EMPLOYEE block. Follow these steps:
POST-QUERY :employee.dept_name := populate_dept_name(:employee.dept_id);
PRE-QUERY DECLARE v_dyn_where VARCHAR2(100) := NULL; BEGIN v_dyn_where := 'WHERE deptno IN (SELECT deptno FROM dept WHERE dname = NVL(:emp.dname, dname) '); SET_BLOCK_PROPERTY('EMP', DEFAULT_WHERE, v_dyn_where); END;
PRE-QUERY is fired even before the SELECT is constructed and is the best place to dynamically change the DEFAULT_WHERE and ORDER BY clauses. PRE-SELECT is fired after the SELECT is constructed and before it is issued for execution. The system variable :SYSTEM.LAST_QUERY holds this SELECT at this point in time. POST-SELECT fires after the SELECT has been executed but before records have been FETCHED . POST-FETCH fires repeatedly for each set of records fetched.
Advanced GUI Development: Developing Beyond GUI
Advanced Forms Programming
Object-oriented Methods in Forms
Intelligence in Forms
Additional Interesting Techniques
Working with Trees
Oracle 8 and 8i Features in Forms Developer