Developer

Oracle Tip: Use the correct looping control in PL/SQL

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. Find out how to get around this problem.

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.

Editor's Picks

Free Newsletters, In your Inbox