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:
- 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.
- 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.
- 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
to ensure that record status is QUERY for each record populated .
- 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.
- 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.