This article originally appeared in the Oracle e-newsletter. Click here to subscribe automatically.

When you process an index-by table in PL/SQL,
there are times when you cannot be sure of the indices that exist
on the table, so you can’t use the most obvious form of the FOR
loop to loop through values. For example:

Declare
    type my list_type is table of number index
by pls_integer;
    mylist mylist_type;
begin
    — start at 2, instead of 1
    mylist(2) := 2;
    mylist(3) := 3;
    — skip 4 and 5
    mylist(6) := 6;
    for i in 1..mylist.count loop
        dbms_output.put_line(mylist(i));

    end loop;
end;
/

The above code will return ORA-01403: no data
found because the index 1 is missing.

If you try to fix the problem
by using the .FIRST/.LAST attributes, you automatically start at
the first index and loop until you reach the last index, like
this:

Declare
    type mylist_type is table of number index
by pls_integer;
    mylist mylist_type;
begin
    mylist(2) := 2;
    mylist(3) := 3;
    mylist(5) := 6;
    for i in mylist.first .. mylist.last loop
        dbms_output.put_lin(mylist(i));

    end loop;
end;
/

However, you still get the ORA-01403: no data
found error because of the skipped values. To avoid this error even
further, you can use the EXISTS method to test an index before
accessing it:

Declare
    type mylist_type is table of number index
by pls_integer;
    mylist mylist_type;
begin
    mylist(2) := 2;
    mylist(3) := 3;
    mylist(6) := 6;
    for i in mylist.first..mylist.last loop
        if
(mylist.exists(i)) then
           
dbms_output.put_line(mylist(i));
        end if;
    end loop;
end;
/

Or, you can use a regular LOOP with the .NEXT
attribute to iterate through the existing indices of a table–but
then you have to declare your own loop counter:

Declare
    type mylist_type is table of number index
by pls_integer;
    mylist mylist_type;
    i pls_integer;
begin
    mylist(2) := 2;
    mylist(3) := 3;
    mylist(6) := 6;
    i := mylist.first;
    loop
        dbms_output.put_line(mylist(i));

        exit when i =
mylist.last;
        i :=
mylist.next(i);
    end loop;
end;
/

You can use either one of these two working
cases as the default method for any loop where you can’t be sure
which values have been deleted or loaded with missing indices. In
case of bulk operations using the FORALL statement, neither of
these techniques will work since a FORALL statement isn’t a true
loop and can only take a lower bound, an upper bound, and a SQL DML
statement in its syntax.

However, in Oracle 10g, two new clauses have
been added to allow you to avoid this limitation. An INDICES OF
clause was added to the FORALL syntax to allow you to automatically
loop through values in a table without worrying about whether the
index exists:

Declare
    type mylist_type is table of number index
by pls_integer;
    mylist mylist_type;
begin
    mylist(2) := 2;
    mylist(3) := 3;
    mylist(6) := 6;
    forall i in indices of mylist
        insert into
mynumtable values(mylist(i));
end;
/

There is also a VALUES OF clause, which uses
the values of your nested table, or indexed by table, as the
subscript of the loop:

Declare
    type mysubs_type is table of pls_integer
index by pls_integer;
    type mylist_type is table of number index
by pls_integer;
    mysubs mylist_type;
    mylist mylist_type;
begin
    mylist(2) := 2;
    mysubs(10) := 2;    —
point to mylist(2)
    mylist(3) := 3;
    mysubs(20) := 3;    —
point to mylist(3)
    mylist(6) := 6;
    mysubs(30) := 6;    —
point to mylist(6)
    mysubs(40) := 3;    —
point to mylist(3), again!
    forall i in values of mysubs
        insert into
mynumtable values(mylist(i));
end;
/

Notice that this syntax allows you to control
the order, and even repeat or omit access to specific records.

Scott Stephens worked for Oracle for more than 13 years in technical support, e-commerce, marketing, and software development. For more of his Oracle tips, visit our Oracle Dev Tips Library.