Oracle COUNT method.

Preamble

In Oracle PL/SQL, the COUNT method is a function that returns the number of items in a collection (ignoring the deleted items even if DELETE stores fillers for them).

Syntax of COUNT collection method in Oracle PL/SQL

collection_name.COUNT;

Parameters or arguments

• collection_name – the name of the collection.
• COUNT – returns the number of elements in the collection.

Consider some examples to understand how to use the COUNT collection method in Oracle PL/SQL.

For Varray COUNT, it is always LAST. If you increase or decrease the size of Varray (using the EXTEND or TRIM method), the value of COUNT will be changed.

The example below shows the COUNT and LAST values for Varray after initialization by four elements after EXTEND (3) and after TRIM (5).

DECLARE
TYPE NumList IS VARRAY(10) OF INTEGER;
n NumList := NumList(1,3,5,7);

PROCEDURE print_count_and_last IS
BEGIN
DBMS_OUTPUT.PUT('n.COUNT = ' || n.COUNT || ', ');
DBMS_OUTPUT.PUT_LINE('n.LAST = ' || n.LAST);
END print_count_and_last;

BEGIN
print_count_and_last;

n.EXTEND(3);
print_count_and_last;

n.TRIM(5);
print_count_and_last;
END;
Result:
n.COUNT = 4, n.LAST = 4
n.COUNT = 7, n.LAST = 7
n.COUNT = 2, n.LAST = 2

COUNT method for Nested Tables

For Nested Tables, COUNT is LAST unless you remove items from the middle of the Nested Tables, in which case COUNT is less than LAST.

The example below shows the COUNT and LAST values for Nested Tables after four elements are initialized, after the third element is removed, and after two zero elements are added to the end. The example shows the state of elements 1 to 8.

DECLARE
TYPE NumList IS TABLE OF INTEGER;
n NumList := NumList(1,3,5,7);

PROCEDURE print_count_and_last IS
BEGIN
DBMS_OUTPUT.PUT('n.COUNT = ' || n.COUNT || ', ');
DBMS_OUTPUT.PUT_LINE('n.LAST = ' || n.LAST);
END print_count_and_last;

BEGIN
print_count_and_last;

n.DELETE(3); -- Delete the third element
print_count_and_last;

n.EXTEND(2); -- Adds two zero elements to the end
print_count_and_last;

FOR i IN 1...8 LOOP
IF n.EXISTS(i) THEN
IF n(i) IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('n(' || i || ') = ' || n(i));
ELSE
DBMS_OUTPUT.PUT_LINE('n(' || i || ') = NULL);
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('n(' || i || ') does not exist');
END IF;
END LOOP;
END;
Result:
n.COUNT = 4, n.LAST = 4
n.COUNT = 3, n.LAST = 4
n.COUNT = 5, n.LAST = 6
n(1) = 1
n(2) = 3
n(3) does not exist
n(4) = 7
n(5) = NULL
n(6) = NULL
n(7) does not exist
n(8) does not exist

PL/SQL tutorial: PL/SQL Collection Method COUNT in Oracle Database

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.