|PL/SQL User's Guide and Reference
10g Release 1 (10.1)
Part Number B10807-01
A collection method is a built-in function or procedure that operates on collections and is called using dot notation. You can use the methods
DELETE to manage collections whose size is unknown or varies.
NEXT are functions that check the properties of a collection or individual collection elements.
DELETE are procedures that modify a collection.
DELETE take integer parameters.
DELETE can also take
VARCHAR2 parameters for associative arrays with string keys.
TRIM cannot be used with index-by tables.
For more information, see "Using Collection Methods".
Keyword and Parameter Description
An associative array, nested table, or varray previously declared within the current scope.
Returns the number of elements that a collection currently contains, which is useful because the current size of a collection is not always known. You can use
COUNT wherever an integer expression is allowed.
COUNT always equals
LAST. For nested tables, normally,
LAST. But, if you delete elements from the middle of a nested table,
COUNT is smaller than
This procedure has three forms.
DELETE removes all elements from a collection.
DELETE(n) removes the
nth element from an associative array or nested table. If
n is null,
DELETE(n) does nothing.
DELETE(m,n) removes all elements in the range
m..n from an associative array or nested table. If
m is larger than
n or if
n is null,
DELETE(m,n) does nothing.
TRUE if the
nth element in a collection exists. Otherwise,
FALSE. Mainly, you use
DELETE to maintain sparse nested tables. You can also use
EXISTS to avoid raising an exception when you reference a nonexistent element. When passed an out-of-range subscript,
FALSE instead of raising
This procedure has three forms.
EXTEND appends one null element to a collection.
n null elements to a collection.
n copies of the
ith element to a collection.
EXTEND operates on the internal size of a collection. If
EXTEND encounters deleted elements, it includes them in its tally.
You cannot use
EXTEND with associative arrays.
LAST return the first and last (smallest and largest) subscript values in a collection. The subscript values are usually integers, but can also be strings for associative arrays. If the collection is empty,
NULL. If the collection contains only one element,
LAST return the same subscript value.
FIRST always returns 1 and
LAST always equals
COUNT. For nested tables, normally,
COUNT. But, if you delete elements from the middle of a nested table,
LAST is larger than
An expression that must return (or convert implicitly to) an integer in most cases, or a string for an associative array declared with string keys.
For nested tables, which have no maximum size,
NULL. For varrays,
LIMIT returns the maximum number of elements that a varray can contain (which you must specify in its type definition).
PRIOR(n) returns the subscript that precedes index
n in a collection.
NEXT(n) returns the subscript that succeeds index
n has no predecessor,
NULL. Likewise, if
n has no successor,
This procedure has two forms.
TRIM removes one element from the end of a collection.
n elements from the end of a collection. If
n is greater than
SUBSCRIPT_BEYOND_COUNT. You cannot use
TRIM with index-by tables.
TRIM operates on the internal size of a collection. If
TRIM encounters deleted elements, it includes them in its tally.
You cannot use collection methods in a SQL statement. If you try, you get a compilation error.
EXISTS can be applied to atomically null collections. If you apply another method to such collections, PL/SQL raises
If the collection elements have sequential subscripts, you can use
collection.FIRST .. collection.LAST in a
FOR loop to iterate through all the elements.
You can use
NEXT to traverse collections indexed by any series of subscripts. For example, you can use
NEXT to traverse a nested table from which some elements have been deleted, or an associative array where the subscripts are string values.
EXTEND operates on the internal size of a collection, which includes deleted elements. You cannot use
EXTEND to initialize an atomically null collection. Also, if you impose the
NULL constraint on a
VARRAY type, you cannot apply the first two forms of
EXTEND to collections of that type.
If an element to be deleted does not exist,
DELETE simply skips it; no exception is raised. Varrays are dense, so you cannot delete their individual elements.
Because PL/SQL keeps placeholders for deleted elements, you can replace a deleted element by assigning it a new value. However, PL/SQL does not keep placeholders for trimmed elements.
The amount of memory allocated to a nested table can increase or decrease dynamically. As you delete elements, memory is freed page by page. If you delete the entire table, all the memory is freed.
In general, do not depend on the interaction between
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
Within a subprogram, a collection parameter assumes the properties of the argument bound to it. You can apply methods
COUNT, and so on to such parameters. For varray parameters, the value of
LIMIT is always derived from the parameter type definition, regardless of the parameter mode.
The following example shows all the collection methods in action:
DECLARE TYPE color_typ IS TABLE OF VARCHAR2(32); colors color_typ; i INTEGER; BEGIN colors := color_typ('red','orange','yellow','green','blue','indigo','violet'); -- Using NEXT is the most reliable way to loop through all elements. i := colors.FIRST; -- get subscript of first element WHILE i IS NOT NULL LOOP colors(i) := INITCAP(colors(i)); dbms_output.put_line('COLORS(' || i || ') = ' || colors(i)); i := colors.NEXT(i); -- get subscript of next element END LOOP; dbms_output.put_line('Deleting yellow...'); colors.DELETE(3); -- Remove item 3. Now the subscripts are 1,2,4,5,6,7. -- Loop goes from 1 to 7, even though item 3 has been deleted. FOR i IN colors.FIRST..colors.LAST LOOP IF colors.EXISTS(i) THEN dbms_output.put_line('COLORS(' || i || ') still exists.'); ELSE dbms_output.put_line('COLORS(' || i || ') no longer exists.'); END IF; END LOOP; dbms_output.put_line('Deleting blue, indigo, violet...'); colors.DELETE(5,7); -- Delete items 5 through 7. -- Loop now goes from 1 to 4, because 4 is the highest ("last") subscript. FOR i IN colors.FIRST..colors.LAST LOOP IF colors.EXISTS(i) THEN dbms_output.put_line('COLORS(' || i || ') still exists.'); ELSE dbms_output.put_line('COLORS(' || i || ') no longer exists.'); END IF; END LOOP; END; /
The following example uses the
LIMIT method to check whether some elements can be added to a varray:
DECLARE TYPE chores_typ is VARRAY(4) OF VARCHAR2(32); chores chores_typ; BEGIN chores := chores_typ('Mow lawn','Wash dishes','Buy groceries'); IF (chores.COUNT + 5) <= chores.LIMIT THEN -- Add 5 more to-do items dbms_output.put_line('Adding 5 more items to CHORES.'); chores.EXTEND(5); ELSE dbms_output.put_line('Can''t extend CHORES, it can hold a maximum of ' || chores.LIMIT || ' items.'); END IF; END; /