DELETE with no indexes specified deletes all elements from collection_name.

DELETE(n) deletes the nth element from an associative array or nested table. If the nth element is null, DELETE(n) does nothing.

DELETE(m,n) deletes all elements in the range m..n from an associative array or nested table. If m is larger than n or if m or n is null, DELETE(m,n) does nothing.

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.

A function that returns the first (smallest) subscript or key value in a collection. If the collection is empty, FIRST returns NULL. If the collection contains only one element, FIRST and LAST return the same subscript value. If the collection is a varray, FIRST always returns 1.

For a collection indexed by integers, FIRST and LAST return the first and last (smallest and largest) index numbers.

For an associative array indexed by strings, FIRST and LAST return the lowest and highest key values. If the NLS_COMP initialization parameter is set to ANSI, the order is based on the sort order specified by the NLS_SORT initialization parameter.

A function that returns the last (largest) subscript value in a collection. If the collection is empty, LAST returns NULL. If the collection contains only one element, FIRST and LAST return the same subscript value. For varrays, LAST always equals COUNT. For nested tables, normally, LAST equals COUNT. But, if you delete elements from the middle of a nested table, LAST is larger than COUNT.

TRIM(n) removes n elements from the end of a collection. If n is greater than COUNT, TRIM(n) raises SUBSCRIPT_BEYOND_COUNT. TRIM operates on the internal size of a collection. If TRIM encounters deleted elements, it includes them in its tally.

A collection method call can appear wherever a PL/SQL subprogram invocation can appear in a PL/SQL statement (but not in a SQL statement).

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

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 PRIOR or NEXT to traverse collections indexed by any series of subscripts. For example, you can use PRIOR or NEXT to traverse a nested table from which some elements were 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 NOTNULL constraint on a TABLE or VARRAY type, you cannot apply the first two forms of EXTEND to collections of that type.

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 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.

Within a subprogram, a collection parameter assumes the properties of the argument bound to it. You can apply methods FIRST, LAST, 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.