Querying by Nonbase Table Items

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.

Tip

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:

  1. Set the Query Allowed property to YES / TRUE for the item DNAME .
  2. Derive the foreign key item DEPT_ID based on the ad hoc query input of :EMPLOYEE.DEPT_NAME. Use the POPULATE_DEPT_NAME procedure to populate the DEPT_NAME item for every DEPT_ID retrieved. Use a POST-QUERY trigger on the EMPLOYEE block to do this:

    POST-QUERY
    
    
    
    :employee.dept_name := populate_dept_name(:employee.dept_id);
    
  3. In the PRE-QUERY trigger, set the DEFAULT_WHERE of the EMP block dynamically based on the resulting DEPTNO :

    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;
    
  4. Performing an Enter Query in the EMPLOYEE block and specifying a criteria like S% in the DEPT_NAME item followed by Execute Query would retrieve only those employees belonging to departments whose names start with uppercase S.

Tip

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.


GUI Development

Advanced GUI Development: Developing Beyond GUI

Multi-form Applications

Advanced Forms Programming

Error-Message Handling

Object-oriented Methods in Forms

Intelligence in Forms

Additional Interesting Techniques

Working with Trees

Oracle 8 and 8i Features in Forms Developer



Oracle Developer Forms Techniques
Oracle Developer Forms Techniques
ISBN: 0672318466
EAN: 2147483647
Year: 2005
Pages: 115

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net