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.
-- dbms_output.put_line('This raise an exception ' || l_parents.count()); -- 1.
where surname = 'Smith'; -- 2.
dbms_output.put_line('Smiths: ' || l_parents.count());
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);
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.