Collection.delete()

We use the delete() method on a PL/SQL collection (associative array, nested table, varray) to delete a single element in it, a subinterval of it, or all its members.

On varray we can call delete() only without argument, to remove all the items in it. The trim() function could be used to remove a single item at the end of the collection.

If we apply delete() on an unitialized nested table or varray, we get a COLLECTION_IS_NULL exception.

As an example, we could modify the code we wrote for testing the associative array inserting a remove call before dumping the data to the output buffer.

If we want to remove all the items, we could write:

l_countries.delete();

We achieve exactely the same result specifying explicitely the range starting from the first element and ending to the last one:

l_countries.delete(l_countries.first(), l_countries.last());

If we want to delete all the items but the extreme ones we could call delete() in this way:

  l_countries.delete(
    l_countries.next(l_countries.first()), 
    l_countries.prior(l_countries.last()));

We have remove all items in the interval starting from the next to te first element and ending to the previous to the last one.

We can explicitely provide the key of the element we want to remove, when we know it. For instance, since Brazil was inserted with key 98, here is how we can remove it:

l_countries.delete(98);

Chapter 12 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein is all about collections.