COUNT Method in PL/SQL – Clear Explanation with Example

The COUNT Method in PL/SQL is an essential function when working with collections like nested tables, varrays, or associative arrays. It helps you determine the number of elements in a collection at any point in your PL/SQL code.

This guide explains how the COUNT method works, its syntax, practical examples, use cases, and best practices — everything you need to become confident with this method.

What is the COUNT Method in PL/SQL?

The COUNT Method in PL/SQL is part of Oracle’s Collection API and is used to return the total number of elements currently present in a collection. It is particularly useful when you want to:

Loop through collection elements.
Perform conditional logic based on collection size.
Prevent runtime errors by checking if the collection has data.

It works with all types of PL/SQL collections:

Nested Tables
VARRAYs
Associative Arrays

Syntax of COUNT Method

collection_name.COUNT

Returns: A PLS_INTEGER value representing the number of existing elements.
Takes Parameters? No
Usable With: Nested tables, varrays, associative arrays.

Example: Using COUNT with a Nested Table

Let’s look at a simple PL/SQL block that demonstrates how the COUNT method works:

DECLARE
   -- Define a nested table type of INTEGER
   TYPE number_table IS TABLE OF INTEGER;

   -- Create a variable of the nested table type and assign values
   number_list number_table := number_table(1, 2, 3, 4, 5);
BEGIN
   -- Display the number of elements using COUNT
   DBMS_OUTPUT.PUT_LINE('How many elements');
   DBMS_OUTPUT.PUT_LINE('------------------');
   DBMS_OUTPUT.PUT_LINE('Count [' || number_list.COUNT || ']');
END;
/

Output of the Program

How many elements
------------------
Count [5]

As you can see, the COUNT method returns the number of values present in the collection, which is 5 in this case.

Real-Life Use Cases of COUNT Method

Here are a few practical uses of the COUNT method in your PL/SQL programs:

1. Looping Through Elements

FOR i IN 1 .. number_list.COUNT LOOP
   DBMS_OUTPUT.PUT_LINE('Value: ' || number_list(i));
END LOOP;

2. Avoiding Errors Before Access

IF number_list.COUNT > 0 THEN
   DBMS_OUTPUT.PUT_LINE('Collection is not empty.');
ELSE
   DBMS_OUTPUT.PUT_LINE('Collection is empty.');
END IF;

3. Dynamic SQL or Logic Based on Size

For example, send a batch insert only if COUNT > 0.

Best Practices When Using COUNT

Use COUNT to check the size before accessing elements by index.
Store the COUNT value in a local variable inside loops to improve performance.
Don’t assume fixed collection sizes — always use COUNT for flexibility.
Combine COUNT with other collection methods like EXTEND, DELETE, and TRIM.

Related Collection Methods

Method	Description
`FIRST`	Returns the lowest subscript in the collection
`LAST`	Returns the highest subscript
`DELETE`	Removes one or all elements
`EXTEND`	Appends empty elements to the end
`TRIM`	Removes elements from the end

🔗 Official Oracle Documentation – PL/SQL Collections

AmantPoint Exclusive Learning Series
© 2025 AmantPoint. All rights reserved. Educational content for developers and learners.