Using Collection Methods in Oracle

The following collection methods help generalize code, make collections easier to use, and make your applications easier to maintain:

EXISTS
COUNT
LIMIT
FIRST and LAST
PRIOR and NEXT
EXTEND
TRIM
DELETE

A collection method is a built-in function or procedure that operates on collections and is called using dot notation. The syntax follows:

collection_name.method_name[(parameters)]

Collection methods cannot be called from SQL statements. Also, EXTEND and TRIM cannot be used with associative arrays. EXISTS, COUNT, LIMIT, FIRST, LAST, PRIOR, and NEXT are functions; EXTEND, TRIM, and DELETE are procedures. EXISTS, PRIOR, NEXT, TRIM, EXTEND, and DELETE take parameters corresponding to collection subscripts, which are usually integers but can also be strings for associative arrays.

Only EXISTS can be applied to atomically null collections. If you apply another method to such collections, PL/SQL raises COLLECTION_IS_NULL.

Checking If a Collection Element Exists (EXISTS Method)

EXISTS(n) returns TRUE if the nth element in a collection exists. Otherwise, EXISTS(n) returns FALSE. Mainly, you use EXISTS with DELETE to maintain sparse nested tables. You can also use EXISTS to avoid raising an exception when you reference a nonexistent element. In the following example, PL/SQL executes the assignment statement only if element i exists:

IF courses.EXISTS(i) THEN courses(i) := new_course; END IF;

When passed an out-of-range subscript, EXISTS returns FALSE instead of raising SUBSCRIPT_OUTSIDE_LIMIT.

Checking the Maximum Size of a Collection (LIMIT Method)

For nested tables and associative arrays, which have no maximum size, LIMIT returns NULL. For varrays, LIMIT returns the maximum number of elements that a varray can contain (which you must specify in its type definition, and can change later with the TRIM and EXTEND methods). For instance, if the maximum size of varray PROJECTS is 25 elements, the following IF condition is true:

IF projects.LIMIT = 25 THEN ... 

You can use LIMIT wherever an integer expression is allowed. In the following example, you use LIMIT to determine if you can add 15 more elements to varray projects:

IF (projects.COUNT + 15) < projects.LIMIT THEN ...