A varray, or variable-size array, is a list with a fixed maximum number of elements, kept in order and without gaps. It suits small lists with a natural limit: the top three performers, the legs of a trip, the phone numbers of a contact. Exceeding the limit is an error, so the limit is enforced by the type itself.
Code for This Guide
The main examples are in the examples/collections folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
type name is varray(max_size) of element_type; create type name as varray(max_size) of element_type; -- usable in SQL
LIMIT returns the maximum size, COUNT the current number of elements. EXTEND adds elements up to the limit, and TRIM removes them from the end; there is no DELETE of single elements, so a varray never has gaps.
A Top-Three List
The varray part of this example extends an empty top-three list by two elements and fills them.
Example:
declare
type t_names is table of varchar2(30); -- nested table
type t_top3 is varray(3) of varchar2(30); -- varray, at most 3 elements
v_crew t_names := t_names('Dubois', 'Müller', 'Laurent');
v_best t_top3 := t_top3();
begin
v_crew.extend;
v_crew(v_crew.last) := 'Zhang';
v_crew.delete(2); -- leaves a gap
dbms_output.put_line('crew: count ' || v_crew.count || ', last index ' || v_crew.last);
v_best.extend(2);
v_best(1) := 'Haddad';
v_best(2) := 'Evans';
dbms_output.put_line('varray: count ' || v_best.count || ' of limit ' || v_best.limit);
dbms_output.put_line('exists(2)? '
|| case when v_crew.exists(2) then 'yes' else 'no' end);
end;
/Output:
crew: count 3, last index 4 varray: count 2 of limit 3 exists(2)? no PL/SQL procedure successfully completed.
It reports a count of 2 of a limit of 3.
The Limit Is Enforced
Example:
declare
type t_top3 is varray(3) of varchar2(30);
v_best t_top3 := t_top3('Haddad', 'Evans', 'Mehta');
begin
dbms_output.put_line('count ' || v_best.count || ' of ' || v_best.limit);
v_best.extend; -- a fourth element
end;
/Output:
count 3 of 3 declare * ERROR at line 1: ORA-06532: subscript outside of limit ORA-06512: at line 6
Extending a full varray raises ORA-06532, subscript outside of limit.
Varray, Nested Table, or Associative Array
| Collection | Size | Gaps | In SQL |
|---|---|---|---|
| Associative array | Unbounded | Any keys | Inside PL/SQL only |
| Nested table | Unbounded | After DELETE | Yes, with a schema-level type |
| Varray | Fixed maximum | Never | Yes, with a schema-level type |
Related Guides
Conclusion
Varrays are ordered lists with a fixed maximum size and no gaps. Use them for small bounded lists, read LIMIT and COUNT to stay within bounds, and choose nested tables or associative arrays when the size has no natural limit.
