Explain PL/SQL collection methods — COUNT, EXISTS, DELETE, EXTEND

How it works

Every collection type exposes the same built-in methods: COUNT (current number of elements), EXISTS(n) (whether index n has an element — essential for sparse nested tables and associative arrays), FIRST/LAST (lowest/highest defined index), DELETE (removes elements — DELETE, DELETE(n), or DELETE(n,m) for a range; not available on VARRAYs), EXTEND (adds room for new elements — only needed on nested tables/VARRAYs, since associative arrays never need pre-allocated slots), and TRIM (removes from the end, VARRAY/nested table only).

DECLARE
  TYPE t_num_tab IS TABLE OF NUMBER;
  v_nums t_num_tab := t_num_tab(10, 20, 30);
BEGIN
  v_nums.EXTEND; -- add one empty slot
  v_nums(4) := 40;
  DBMS_OUTPUT.PUT_LINE('Count: ' || v_nums.COUNT);

  v_nums.DELETE(2);
  DBMS_OUTPUT.PUT_LINE('Exists(2): ' || CASE WHEN v_nums.EXISTS(2) THEN 'Y' ELSE 'N' END);
  DBMS_OUTPUT.PUT_LINE('First: ' || v_nums.FIRST || ', Last: ' || v_nums.LAST);
END;
/

Related Questions