19/03/2016

[SQL] Oracle count elements in nested table

When working with collections in Oracle, you might need to count the number of elements they contain.

In the case of Nested Tables, you can either COUNT(*) after you unnest them:

SELECT COUNT(nt.*)
FROM myTable t, TABLE(t.nested_table) nt

or use the far simpler CARDINALITY function (returns NULL in case of empty list):

SELECT NVL(CARDINALITY(t.nested_table), 0)
FROM myTable t

In case of VARRAYs instead, you must still COUNT the elements but if you're working in PL/SQL though, you can go with:

myVarray.COUNT

No comments:

Post a Comment

With great power comes great responsibility