11.5.7.1 Boundary considerations

If PRIOR and NEXT are applied to initialized collections that have no elements, they return NULL. If i is greater than or equal to COUNT, NEXT returns NULL; if i is less than or equal to FIRST, PRIOR returns NULL.

Currently, if the collection has elements, and i is greater than COUNT, PRIOR returns LAST; if i is less than FIRST, NEXT returns FIRST. However, do not rely on this behavior in future Oracle versions.

11.5.7.2 Exceptions possible

If PRIOR and NEXT are applied to an uninitialized nested table or a VARRAY, they raise the COLLECTION_ IS_NULL predefined exception.

11.5.8 The trim Method

Use TRIM to remove n elements from the end of a nested table orVARRAY. Without arguments, TRIM removes exactly one element. As we've already mentioned, confusing behavior occurs if you combine DELETE and TRIM actions on a collection; for example, if an element that you are trimming has previously been DELETEd, TRIM "repeats" the deletion but counts this as part of n, meaning that you may be TRIMming fewer actual elements than you think.

Attempting to TRIM an associative array will produce a compile-time error.

The specification for TRIM is:

PROCEDURE TRIM (n PLS_INTEGER:=1);

The following function pops the last value off of a list and returns it to the invoking block. The "pop" action is implemented by trimming the collection by a single row after extracting the value.

CREATE FUNCTION pop (the_list IN OUT List_t) RETURN VARCHAR2

AS

l_value VARCHAR2(30);

BEGIN

IF the_list.COUNT >= 1

THEN

/* Save the value of the last element in the collection

|| so it can be returned

*/

l_value := the_list(the_list.LAST);

the_list.TRIM;

END IF;

RETURN l_value;

END;

11.5.8.1 Boundary considerations

If n is null, TRIM will do nothing.

11.5.8.2 Exceptions possible

The TRIM method will raise the SUBSCRIPT_BEYOND_COUNT predefined exception if you attempt to TRIM more elements than actually exist. If TRIM is applied to an uninitialized nested table or a VARRAY, it raises the COLLECTION_IS_NULL predefined exception.

If you use TRIM and DELETE on the same collection, you can get some very surprising results. Consider this scenario: if you DELETE an element at the end of a nested table variable and then do a TRIM on the same variable, how many elements have you removed? You might think that you have removed two elements, but, in fact, you have removed only one. The placeholder that is left by DELETE is what TRIM acts upon. To avoid confusion, Oracle Corporation recommends using either DELETE or TRIM, but not both, on a given collection.

11.6 Working with Collections

By now, you have learned how to define a TYPE of collection, whether in the database or in your code base. You also know how to declare collections based on those types. Now let's examine the kind of code you need to write to actually work with those collections. There are three main programming tasks you need to understand:

• How to properly initialize the collection variable

• How to assign values to elements without raising exceptions

• How to add and remove "slots" for elements

In addition, to fully exploit the programming utility of collections, you need to learn how to retrieve and store sets of data with them. This leads into our section on pseudo-functions, which allow you to perform magic tricks with collections. (OK, maybe it's not real magic, but you're almost guaranteed to say "How did they do that?" the first time you try to program this stuff.)