Use of Other Built-in Methods for Collections

These collection methods can be used in a variety of ways. In the case of a nested table where some of the values are null, selection using a standard cursor could result in an exception. Use the FIRST and NEXT collection methods to transverse these null values. The following code sample illustrates an interesting use of several collection methods:

j := dependents.FIRST;
WHILE j IS NOT NULL LOOP
 process dependents(j)
   j := dependents.NEXT(j);
END LOOP

So what does this code fragment do? First, we use the FIRST collection method to get the index integer of the first valid record for the dependents nested table. Remember that a nested table is a sparse construct so the first valid value may not be one. Next, we begin loop processing of values assuming we didn’t get a null value on our call to FIRST. Once we have processed the first value we reset out counter to the appropriate NEXT value. Once NEXT evaluates to NULL we exit the loop.

SEE CODE DEPOT FOR FULL SCRIPTS

If you like Oracle tuning, you may enjoy the book Oracle Tuning: The Definitive Reference , with over 900 pages of BC's favorite tuning tips & scripts.  You can buy it directly from the publisher and save 30%, and get instant access to the code depot of Oracle tuning scripts.