Collection.count()

We could check the number of elements in a PL/SQL collection (associative array, nested table, varray) using the count() function.

In case of a collection that requires initialization (nested table and varray), if its count() method is called before it is initialized, a COLLECTION_IS_NULL exception is raised.

Here is an example that uses the example data we created in the previous post on varray.

declare
  l_parents parents_t;
begin
--  dbms_output.put_line('This raise an exception ' || l_parents.count()); -- 1.

  select parents
  into l_parents
  from family
  where surname = 'Smith'; -- 2.

  dbms_output.put_line('Smiths: ' || l_parents.count());
exception
  when collection_is_null then
    dbms_output.put_line('Collection is null');
  when no_data_found then
    dbms_output.put_line('No data found');
  when others then -- 3.
    dbms_output.put_line('Unexpected: ' || sqlerrm);
end;

1. At this point l_parents has not been initialized, if you uncomment this line you would get a COLLECTION_IS_NULL exception.
2. If no family with surname Smith is in the table, we have a NO_DATA_FOUND exception
3. No other exceptions are expected in this piece of code.

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