A nested table is an unbounded list: it can grow without limit, can have gaps after deletions, and, when its type is created at schema level, can be used in SQL as a table or as a column type. That makes it the collection to choose when a list of values must travel between PL/SQL and SQL, such as a set of codes to filter a query by.
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 table of element_type; -- in PL/SQL create type name as table of element_type; -- at schema level, usable in SQL v_list name := name(value, ...); -- constructor
A nested table starts with the elements given to its constructor. EXTEND adds room for new elements, DELETE removes elements and leaves gaps, and TRIM removes elements from the end.
Grow, Delete, and Check
The crew list is extended with a fourth name, then the second element is deleted, which leaves a gap. The varray part of the example is covered separately.
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.
After the delete, COUNT is 3 but LAST is still 4, and EXISTS(2) is false: loop with FIRST and NEXT, or INDICES OF, when there can be gaps.
Extend Before Assigning
Example:
declare
type t_names is table of varchar2(30);
v_crew t_names := t_names('Dubois', 'Müller');
begin
dbms_output.put_line('count ' || v_crew.count);
v_crew(3) := 'Zhang'; -- no EXTEND first
end;
/Output:
count 2 declare * ERROR at line 1: ORA-06533: Subscript beyond count ORA-06512: at line 6
Assigning beyond the current count raises ORA-06533; call EXTEND first.
Use It in SQL with TABLE()
A nested table of a schema-level type can be joined in a query with TABLE(), passing a list of values to one statement. The example creates the type first:
Setup:
create or replace type t_code_list as table of varchar2(3);
Example:
declare
v_codes t_code_list := t_code_list('SIN', 'NRT', 'KTM');
begin
for r in (select a.airport_code, a.city
from airports a
join table(v_codes) c on c.column_value = a.airport_code
order by a.airport_code) loop
dbms_output.put_line(r.airport_code || ' ' || r.city);
end loop;
end;
/Output:
KTM Kathmandu NRT Tokyo SIN Singapore PL/SQL procedure successfully completed.
The elements appear in the column COLUMN_VALUE. Drop the type afterward with drop type t_code_list.
Related Guides
Conclusion
Nested tables are unbounded lists that can have gaps, built with a constructor and grown with EXTEND. Create the type at schema level to use them in SQL with TABLE() or as columns, and use EXISTS, FIRST, and NEXT when elements may be missing.
