I mentioned in the previous tutorial that there are 7 collection functions. Among these seven collection function – COUNT ( ) is the first one which we are going to explore in this tutorial. If you are following this series of PL/SQL Collection then you must have already came across this collection function.  But as of today we have dedicated a full blog to this topic thus we will take the liberty and explore the collection method COUNT ( ) in detail.

What is Collection Method COUNT ( )?

Collection method COUNT ( ) returns the number of elements in an initialize collection. If used with an initialize collection with no elements; it returns zero.

Confused! Why we denote collection functions and procedures as Collection Method? Read Introduction to collection Methods to find out the answer.

When does Collection Method COUNT ( ) return zero?

Collection method COUNT ( ) returns zero when it is applied or say used with an initialize collection (i.e. VARRAYs & Nested Tables) with no elements. It also returns zero as a result when it is used with an empty associated array.

Signature of Collection Method COUNT ( )?

The signature of the function COUNT is –

FUNCTION COUNT RETURN PLS_INTEGER;

Suggested Reading: PL/SQL Functions

Does collection method COUNT ( ) works the same with a Nested Table?

No. This is because COUNT ( ), returns the number of non-empty elements in a nested table since it is possible for a collection nested table to have individual elements that are empty.

Why the heck am I getting ‘Collection_IS_NULL’ error?

It seems like you are using COUNT ( ) with an uninitialized collection. Whenever you apply collection function COUNT ( ) to an uninitialized collection (i.e. Nested Tables & VARRAYs) it raises the ‘Collection_Is_Null’ exception which is a pre-defined exception in Oracle Database.

As Associative Arrays don’t require initialization, thus you will not get this exception with them. You can read more about Associative arrays here.

Examples of Collection Method COUNT ( )

Example 1: Compute total number of elements stored in a Nested table.

You can use COUNT ( ) function to compute total number of elements stored into a collection such as Nested table.

SET SERVEROUTPUT ON;
DECLARE
    TYPE my_nested_table IS TABLE OF number;
    var_nt my_nested_table := my_nested_table (9,18,27,36,45,54,63,72,81,90);
BEGIN
    DBMS_OUTPUT.PUT_LINE ('The Size of the Nested Table is ' ||var_nt.count);
END;
/

Go ahead; copy and paste the above code in your IDE and see the result.

Example 2. COUNT ( ) function with IF Condition

You can use COUNT ( ) function to control the flow of the program using a condition. So let’s write a very simple program which will demonstrate how to use collection Method COUNT ( ) with IF Condition.

SET SERVEROUTPUT ON;
DECLARE
    TYPE my_nested_table IS TABLE OF number;
    var_nt my_nested_table := my_nested_table (9,18,27,36,45,54,63,72,81,90);
BEGIN
    IF var_nt.count >= 10 THEN
        DBMS_OUTPUT.PUT_LINE (‘you have already inserted 10 elements in your Nested table.');
        DBMS_OUTPUT.PUT_LINE ('Are you sure you want to insert more?');
    END IF;
END;
/

Similarly you can use the collection method COUNT ( ) with Loops.  You can watch the PL/SQL tutorial on the same topic to learn to do that. You can find the video here.

That is a very simple demonstration. I am sure you can come-up with some crazier examples. So what are you waiting for, go ahead and write your code and see in what other possible ways you can use this method.

If you want me to review your code then you can share your code with me on my Facebook page or on my Twitter as well.

How do you like this blog? Is there anything you want us to improve? Tell us, what you feel on our Facebook page and on our Twitter.

Thanks and have a great day!