Oracle PL/SQL Best Practices

Name

CTL-08: Scan collections using FIRST, LAST, and NEXT in loops.

Synopsis

A collection in PL/SQL is like a single-dimensional array. A collection differs from an array, however, in that two of the three types of collections (nested tables and index-by tables) can be sparse, which means that the defined rows in the collection need not be sequentially defined. You can, in other words, assign a value to row 10 and a value to row 10,000, and now rows will exist between those two.

If you scan a collection with a FOR loop and the collection is sparse, the FOR loop tries to access an undefined row and raise a NO_DATA_FOUND exception. Instead, use the FIRST and NEXT methods to scan forward through a collection, and use LAST and PRIOR to scan backwards

Example

I have decided to help all of my co-programmers by providing a package that offers a standard collection type (list of strings) and some utility programs to manipulate collections defined on that type. Here is the package specification:

CREATE OR REPLACE PACKAGE mycollection
IS
   TYPE string_tt IS TABLE OF VARCHAR2 (2000)
      INDEX BY BINARY_INTEGER;

   PROCEDURE show (list_in IN string_tt);

   FUNCTION eq (list1_in IN string_tt, list2_in IN string_tt)
      RETURN BOOLEAN;
END mycollection;

By using this package, I can easily declare a collection, display its contents, and even compare two collections of the same type to see if they are equal. That sounds handy! The implementation of this utility package, however, will determine how widely my code is ...