An associative array is PL/SQL's map: it stores values under keys, either integers or strings, grows as you assign, and returns its keys in sorted order. It is the natural structure for lookups loaded once and used many times, such as city names by airport code, without querying the table again for every row.
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 index by {pls_integer | varchar2(n)};Assigning to a key creates the element. FIRST, NEXT, PRIOR, and LAST walk the keys in order; EXISTS tests a key; COUNT returns the number of elements; DELETE removes one or all.
A Lookup by Airport Code
Example:
declare
type t_city_by_code is table of varchar2(40) index by varchar2(3);
v_cities t_city_by_code;
v_code varchar2(3);
begin
for a in (select airport_code, city from airports where country_code in ('IN', 'US')) loop
v_cities(a.airport_code) := a.city;
end loop;
dbms_output.put_line('BOM is ' || v_cities('BOM') || '; ' || v_cities.count || ' cities');
v_code := v_cities.first; -- keys come back sorted
while v_code is not null loop
dbms_output.put_line(v_code || ' ' || v_cities(v_code));
v_code := v_cities.next(v_code);
end loop;
end;
/Output:
BOM is Mumbai; 6 cities BLR Bengaluru BOM Mumbai DEL Delhi JFK New York LAX Los Angeles ORD Chicago PL/SQL procedure successfully completed.
The loop fills the array from a query. Reading v_cities('BOM') returns Mumbai, and walking from FIRST with NEXT returns the codes in sorted order, whatever order they were inserted in.
Missing Keys
Reading a key that does not exist raises NO_DATA_FOUND, so test with EXISTS first when a key may be missing.
Example:
declare
type t_counts is table of pls_integer index by varchar2(3);
v_routes t_counts;
begin
for r in (select origin, count(*) as n from routes group by origin) loop
v_routes(r.origin) := r.n;
end loop;
dbms_output.put_line('DXB has ' || v_routes('DXB') || ' routes');
if v_routes.exists('KTM') then
dbms_output.put_line('KTM has ' || v_routes('KTM'));
else
dbms_output.put_line('KTM has no entry');
end if;
dbms_output.put_line(v_routes('KTM')); -- reading a missing key
end;
/Output:
DXB has 20 routes KTM has no entry declare * ERROR at line 1: ORA-01403: no data found ORA-06512: at line 12
Kathmandu has no routes, so it has no entry; EXISTS handles that, while reading the key directly raises ORA-01403.
Things to Know
- Associative arrays exist only in PL/SQL; they cannot be columns. Inside PL/SQL, TABLE() can still query them in SQL.
- String keys are ordered by the session's sort; integer keys can be sparse and negative.
- Fill them in one step with BULK COLLECT, or with a qualified expression.
Related Guides
Conclusion
Associative arrays map integer or string keys to values in PL/SQL, grow on assignment, and return keys in sorted order. Use them as in-memory lookups, test keys with EXISTS, and walk them with FIRST and NEXT or a FOR ... INDICES OF loop.
