Taking Advantage of PL/SQL 2.x in Forms

Taking Advantage of PL SQL 2 x in Forms

In many applications, getting a huge number of records into a base-table block becomes necessary, especially when a search based on user criteria is involved . Search here means locating a particular record in the entire result set with the usual functions of KEY-UP and KEY-DOWN, and Find First and Find Next in case of multiple matches (for example, when the user types in S% for the search). This search should not be confused with a filter, which involves a dynamically shrinking or expanding result set. Even a filter may return a large result set. The usual procedure is to do an EXECUTE_QUERY in the block and to loop through until a match is found. In a client/server environment, it involves buffering of disk space, as well as a time delay in locating the desired record. For example, locating a record after the 7000th in a result set of 10,000 could cause buffer allocation if the record- size is large and client-side disk space is insufficient. It also has a significant time delay.

A more elegant solution to this problem is to replace the normal Forms buffering and fetching process and manually control record fetching and block population. That Forms 5.0 supports PL/SQL 2.x is an advantage in this kind of a situation. The trick lies in using a PL/SQL table of records in Forms 5.0. The search process then follows a new algorithm that saves disk buffering, as well as improves location time significantly.

The new algorithm works as follows:

• No EXECUTE_QUERY is done.
• A Find essentially involves a filter with the result set containing only those records that match the Find criteria.
• At any point of time, only n number of records are brought into the block. This involves fetching all records into a PL/SQL table of records and populating the block with these n records manually. If the number of database trips is not a problem, even records can be fetched in chunks of n.
• A Find Next operation essentially involves navigating to the next record in the block until the n records are exhausted, then fetching the next chunk, clearing the block, and repopulating the block with this new chunk .
• A Find First operation involves populating the block with the very first set of n records that match the Find criteria.
• KEY-UP and KEY-DOWN work fine for the n records in the block, and choosing the value of n to be a large number, say 100, will suffice ( assuming that any user will navigate within a block, either up or down, not more than 100 times at any time). Also, beyond this range, it should not be a problem because you have the whole result set in the PL/SQL table and keeping track of the indexes in the current result set will help in getting the preceding or next record.

You implement this technique with these steps:

1. Declare a PL/SQL cursor with a SELECT statement selecting all the base table columns in the block and having a WHERE clause specifying the Find criteria and an ORDER BY clause. When the WHERE and ORDER BY clauses are dynamic, the whole algorithm has to be implemented using dynamic SQL.
2. Declare at least two PL/SQL tables of records, with each element being a record type similar to the declared cursor record type. Populate one of these tables (say, FIRST_SET ) with the very first set of n records for the Find First function and another table (say, WHOLE_SET ) with the entire result set from the cursor.
3. Loop through the PL/SQL table WHOLE_SET iterating from index to (index+n “1), where index equals 1 for the very first set and index equals n+1 for the subsequent sets. Do a CLEAR_BLOCK first. Then, using CREATE_RECORD, populate the block with the table element values and use

   SET_RECORD_PROPERTY(:SYSTEM.CURSOR_RECORD, ,
   
    STATUS, QUERY_STATUS)
   

   to ensure that record status is QUERY for each record populated .

4. In step 3, for index = 1, populate the table FIRST_SET using TABLE_FROM_BLOCK immediately after populating the block. This will store the first set of records for the Find First function.
5. KEY-UP and KEY-DOWN should not be a problem beyond the amplitude of (index, index+n-1) because all the records are in the WHOLE_SET index-by table. You have to keep track only of the PL/SQL table index for the current set to get the preceding or next record.