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.

l_parents parents_t;
-- 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());
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.

No comments:

Post a Comment