CARDINALITY function in Oracle

In Oracle, the CARDINALITY function is primarily used in the context of the TABLE operator and collections, particularly when dealing with nested tables or VARRAYs in PL/SQL. It returns the number of elements in a collection.

---

🔹 Syntax:

CARDINALITY(collection)

• collection: A PL/SQL nested table or VARRAY.

---

🔹 Example 1: Using CARDINALITY with Nested Table

DECLARE
  TYPE num_table IS TABLE OF NUMBER;
  v_numbers num_table := num_table(10, 20, 30, 40);
  v_count   NUMBER;
BEGIN
  v_count := CARDINALITY(v_numbers);
  DBMS_OUTPUT.PUT_LINE('Number of elements: ' || v_count);
END;

🟢 Output:

Number of elements: 4

---

🔹 Example 2: Using CARDINALITY in SQL with TABLE operator

Suppose you have a collection type and use it in a SQL query:

-- Create a type at SQL level
CREATE OR REPLACE TYPE num_list AS TABLE OF NUMBER;
/

-- Use in a SELECT
SELECT CARDINALITY(CAST(COLLECT(column_value) AS num_list)) AS element_count
FROM TABLE(num_list(100, 200, 300));

🟢 Output:

ELEMENT_COUNT
-------------
3

---

🔹 Notes:

• CARDINALITY is only valid for collection types, not for regular tables.
• For regular tables, use COUNT(*) instead.

---