Using Collection Methods in Oracle

Finding the First or Last Collection Element (FIRST and LAST Methods)

FIRST and LAST return the first and last (smallest and largest) index numbers in a collection. For an associative array with VARCHAR2 key values, the lowest and highest key values are returned; ordering is based on the binary values of the characters in the string, unless the NLS_COMP initialization parameter is set to ANSI, in which case the ordering is based on the locale-specific sort order specified by the NLS_SORT initialization parameter.

If the collection is empty, FIRST and LAST return NULL.

If the collection contains only one element, FIRST and LAST return the same index value:

IF courses.FIRST = courses.LAST THEN ...  -- only one element

The next example shows that you can use FIRST and LAST to specify the lower and upper bounds of a loop range provided each element in that range exists:

FOR i IN courses.FIRST..courses.LAST LOOP ...

In fact, you can use FIRST or LAST wherever an integer expression is allowed. In the following example, you use FIRST to initialize a loop counter:

i := courses.FIRST;
WHILE i IS NOT NULL LOOP ...

For varrays, FIRST always returns 1 and LAST always equals COUNT. For nested tables, FIRST normally returns 1. But, if you delete elements from the beginning of a nested table, FIRST returns a number larger than 1. Also for nested tables, LAST normally equals COUNT. But, if you delete elements from the middle of a nested table, LAST becomes larger than COUNT.

When scanning elements, FIRST and LAST ignore deleted elements.

Looping Through Collection Elements (PRIOR and NEXT Methods)

PRIOR(n) returns the index number that precedes index n in a collection. NEXT(n) returns the index number that succeeds index n. If n has no predecessor, PRIOR(n) returns NULL. Likewise, if n has no successor, NEXT(n) returns NULL.

For associative arrays with VARCHAR2 keys, these methods return the appropriate key value; ordering is based on the binary values of the characters in the string, unless the NLS_COMP initialization parameter is set to ANSI, in which case the ordering is based on the locale-specific sort order specified by the NLS_SORT initialization parameter.

These methods are more reliable than looping through a fixed set of subscript values, because elements might be inserted or deleted from the collection during the loop. This is especially true for associative arrays, where the subscripts might not be in consecutive order and so the sequence of subscripts might be (1,2,4,8,16) or ('A','E','I','O','U').

PRIOR and NEXT do not wrap from one end of a collection to the other. For example, the following statement assigns NULL to n because the first element in a collection has no predecessor:

n := courses.PRIOR(courses.FIRST);  -- assigns NULL to n

PRIOR is the inverse of NEXT. For instance, if element i exists, the following statement assigns element i to itself:

projects(i) := projects.PRIOR(projects.NEXT(i));

You can use PRIOR or NEXT to traverse collections indexed by any series of subscripts. In the following example, you use NEXT to traverse a nested table from which some elements have been deleted:

i := courses.FIRST;  -- get subscript of first element
WHILE i IS NOT NULL LOOP
   -- do something with courses(i) 
   i := courses.NEXT(i);  -- get subscript of next element
END LOOP;

When traversing elements, PRIOR and NEXT ignore deleted elements.

Decreasing the Size of a Collection (TRIM Method)

This procedure has two forms:

• TRIM removes one element from the end of a collection.
• TRIM(n) removes n elements from the end of a collection.

For example, this statement removes the last three elements from nested table courses:

courses.TRIM(3);

If n is too large, TRIM(n) raises SUBSCRIPT_BEYOND_COUNT.

TRIM operates on the internal size of a collection. So, if TRIM encounters deleted elements, it includes them in its tally. Consider the following example:

DECLARE
   TYPE CourseList IS TABLE OF VARCHAR2(10); 
   courses CourseList;
BEGIN
   courses := CourseList('Biol 4412', 'Psyc 3112', 'Anth 3001');
   courses.DELETE(courses.LAST);  -- delete element 3
   /* At this point, COUNT equals 2, the number of valid
      elements remaining. So, you might expect the next 
      statement to empty the nested table by trimming 
      elements 1 and 2. Instead, it trims valid element 2 
      and deleted element 3 because TRIM includes deleted 
      elements in its tally. */
   courses.TRIM(courses.COUNT);
   dbms_output.put_line(courses(1));  -- prints 'Biol 4412'

In general, do not depend on the interaction between TRIM and DELETE. It is better to treat nested tables like fixed-size arrays and use only DELETE, or to treat them like stacks and use only TRIM and EXTEND.

PL/SQL does not keep placeholders for trimmed elements. So, you cannot replace a trimmed element simply by assigning it a new value.