Explain VARRAY collections in PL/SQL

How it works

A VARRAY (TYPE ... IS VARRAY(n) OF element_type) is a fixed-maximum-size, always-dense array — elements keep their order, can't be deleted individually (only trimmed from the end), and when persisted to a table column the whole collection is stored as a single object, unlike a nested table's separate-storage-table model. Its bounded size makes it a natural fit whenever the maximum count is known upfront.

DECLARE
  TYPE t_top3_scores IS VARRAY(3) OF NUMBER;
  v_scores t_top3_scores := t_top3_scores(95, 88, 76);
BEGIN
  FOR v_i IN 1..v_scores.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_scores(v_i));
  END LOOP;
  v_scores.TRIM; -- removes the last element
  DBMS_OUTPUT.PUT_LINE('Count after TRIM: ' || v_scores.COUNT);
END;
/

Related Questions