Dense and sparse collections
declare
type colors_t is table of varchar2(100) index by pls_integer;
l_colors_dense colors_t;
l_colors_sparse colors_t;
begin
l_colors_dense(100) := 'Blue';
l_colors_dense(101) := 'Green';
l_colors_sparse(1) := 'Red';
l_colors_sparse(3) := 'Yellow';
end;
Statement processed.
Basic for loop with dense collection
declare
type colors_t is table of varchar2(100) index by pls_integer;
l_colors colors_t := colors_t (1 => 'Blue', 2 => 'Green', 3 => 'Yellow');
begin
for indx in 1 .. l_colors.count
loop
dbms_output.put_line (l_colors (indx));
end loop;
end;
Statement processed.
Blue
Green
Yellow
What if index does not start at 1?
declare
type colors_t is table of varchar2(100) index by pls_integer;
l_colors colors_t;
begin
l_colors (100) := 'Blue';
l_colors (101) := 'Green';
for indx in 1 .. l_colors.count
loop
dbms_output.put_line (l_colors (indx));
end loop;
end;
ORA-01403: no data found ORA-06512: at line 10 ORA-06512: at "SYS.DBMS_SQL", line 1721More Details: https://docs.oracle.com/error-help/db/ora-00100
What's going on "behind the scenes"
declare
type colors_t is table of varchar2(100) index by pls_integer;
l_colors colors_t;
begin
l_colors (100) := 'Blue';
l_colors (101) := 'Green';
dbms_output.put_line (l_colors (1));
dbms_output.put_line (l_colors (2));
end;
ORA-01403: no data found ORA-06512: at line 8 ORA-06512: at "SYS.DBMS_SQL", line 1721More Details: https://docs.oracle.com/error-help/db/ora-00100
Using first and last
declare
type colors_t is table of varchar2(100) index by pls_integer;
l_colors colors_t;
begin
l_colors(100) := 'Blue';
l_colors(101) := 'Green';
for indx in l_colors.first .. l_colors.last
loop
dbms_output.put_line (l_colors (indx));
end loop;
end;
Statement processed.
Blue
Green
If collection is empty watch out for value_error!
declare
type colors_t is table of varchar2(100) index by pls_integer;
l_colors colors_t;
begin
for indx in l_colors.first .. l_colors.last
loop
dbms_output.put_line (l_colors (indx));
end loop;
end;
ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 5 ORA-06512: at "SYS.DBMS_SQL", line 1721More Details: https://docs.oracle.com/error-help/db/ora-06502
Protection from ORA-06502
declare
type colors_t is table of varchar2(100) index by pls_integer;
l_colors colors_t;
begin
for indx in nvl (l_colors.first, 1) .. nvl (l_colors.last, 0)
loop
dbms_output.put_line (l_colors (indx));
end loop;
end;
Statement processed.
Avoiding no_data_found with exception handler
declare
type colors_t is table of varchar2(100) index by pls_integer;
l_colors colors_t;
begin
l_colors(100) := 'Blue';
l_colors(1000) := 'Green';
for indx in l_colors.first .. l_colors.last
loop
begin
dbms_output.put_line (l_colors (indx));
exception when no_data_found then null;
end;
end loop;
end;
Statement processed.
Blue
Green
Avoiding no_data_found with exists
declare
type colors_t is table of varchar2(100) index by pls_integer;
l_colors colors_t;
begin
l_colors(100) := 'Blue';
l_colors(1000) := 'Green';
for indx in l_colors.first .. l_colors.last
loop
if l_colors.exists (indx)
then
dbms_output.put_line (l_colors (indx));
end if;
end loop;
end;
Statement processed.
Blue
Green
Navigating with first and last
declare
type colors_t is table of varchar2(100) index by pls_integer;
l_colors colors_t;
l_index pls_integer;
begin
l_colors(100) := 'Blue';
l_colors(1000) := 'Green';
l_index := l_colors.first;
while l_index is not null
loop
dbms_output.put_line (l_colors (l_index));
l_index := l_colors.next (l_index);
end loop;
end;
Statement processed.
Blue
Green
Using SQL to iterate through tables
create or replace type list_of_names_t is table of varchar2(100);
Type created.
declare
happyfamily list_of_names_t := list_of_names_t('Kim', 'Kourtney', 'Khloe', 'Rob');
begin
for rec in (
select column_value family_name
from table ( happyfamily )
order by family_name
) loop
DBMS_OUTPUT.put_line(rec.family_name);
end loop;
end;
Statement processed.
Khloe
Kim
Kourtney
Rob
Sweet new syntax in Oracle Database 21c!
declare
type colors_t is table of varchar2(100) index by pls_integer;
l_colors colors_t;
begin
l_colors(100) := 'Blue';
l_colors(1000) := 'Green';
for indx in indices of l_colors
loop
dbms_output.put_line (l_colors (indx));
end loop;
end;
ORA-06550: line 8, column 24: PLS-00103: Encountered the symbol "OF" when expecting one of the following: . ( * @ % & - + / at loop mod remainder rem .. <an exponent (**)> || multiset The symbol ". was inserted before "OF" to continue.More Details: https://docs.oracle.com/error-help/db/ora-06550