Bulk fetches and the LIMIT clause gotcha

I have been reading Tim Hall's book on Oracle PL/SQL Tuning (do I know how to have a swinging festive season or what?). I'll write later about the whole book but I noticed a problem in an example in the chapter on bulk binds. It's the sort of thing which is easily missed until it bites you on the backside.

Here is a slightly compacted version of Tim's example...

SQL> create table t262 as select * from all_objects
  2  where rownum <= 262;

Table created.

SQL> 
SQL> set serveroutput on
SQL> 
SQL> declare
  2      type r262 is table of t262%rowtype;
  3      l_data r262;
  4      cursor c_data is
  5          select * from t262;
  6  begin
  7      open c_data;
  8      loop
  9          fetch c_data bulk collect into l_data limit 100;
 10          --  Process contents of collection here
 11          dbms_output.put_line('rowcount='||l_data.count());
 12          exit when c_data%notfound;
 13      end loop;
 14      close c_data;
 15  end;
 16  /
rowcount=100
rowcount=100
rowcount=62

PL/SQL procedure successfully completed.

SQL> 

So where is the problem? Well, if we replace Tim's comment with some actual processing and work with a resultset which just happens to be an exact multiple of the LIMIT clause we find this ugliness...

SQL> declare
  2      type r262 is table of t262%rowtype;
  3      l_data r262;
  4      cursor c_data is
  5          select * from t262
  6          where rownum <= 200;
  7      n pls_integer := 0;
  8  begin
  9      open c_data;
 10      loop
 11          fetch c_data bulk collect into l_data limit 100;
 12          dbms_output.put_line('rowcount='||l_data.count());
 13          for i in l_data.first()..l_data.last() loop
 14              n := n+1;
 15          end loop;
 16          exit when c_data%notfound;
 17      end loop;
 18      dbms_output.put_line('total rows processed ='||n);
 19      close c_data;
 20  end;
 21  /
rowcount=100
rowcount=100
rowcount=0
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 13

SQL> 

The temptation is to solve this by moving the %NOTFOUND check ahead of the processing. Unfortunately %NOTFOUND checks whether the FETCH returned the exact number of rows specified by the LIMIT clause, not whether it returned any rows at all ...

SQL> declare
  2      type r262 is table of t262%rowtype;
  3      l_data r262;
  4      cursor c_data is
  5          select * from t262;
  6      n pls_integer := 0;
  7  begin
  8      open c_data;
  9      loop
 10          fetch c_data bulk collect into l_data limit 100;
 11          dbms_output.put_line('rowcount='||l_data.count());
 12          exit when c_data%notfound;
 13          for i in l_data.first()..l_data.last() loop
 14              n := n+1;
 15          end loop;
 16      end loop;    
 17      dbms_output.put_line('total rows processed ='||n);   
 18      close c_data;
 19  end;
 20  /
rowcount=100
rowcount=100
rowcount=62
total rows processed =200

PL/SQL procedure successfully completed.

SQL> 

So if we want to process the whole resultset we need to use the collection COUNT() built-in to check whether any rows have been found.

SQL> declare
  2      type r1000 is table of t262%rowtype;
  3      l_data r1000;
  4      cursor c_data is
  5          select * from t262;
  6      n pls_integer := 0;
  7  begin
  8      open c_data;
  9      loop
 10          fetch c_data bulk collect into l_data limit 100;
 11          dbms_output.put_line('rowcount='||l_data.count());
 12          exit when l_data.count() = 0;
 13          for i in l_data.first()..l_data.last() loop
 14              n := n+1;
 15          end loop;
 16      end loop;
 17      dbms_output.put_line('total rows processed ='||n);   
 18      close c_data;
 19  end;
 20  /
rowcount=100
rowcount=100
rowcount=62
rowcount=0
total rows processed =262

PL/SQL procedure successfully completed.

SQL> 

This interaction between %NOTFOUND and the LIMIT clause is not entirely intuitive so it is unfortunate that it is not explained in the documentation. The PL/SQL Developer's Guide gives an example which checks for %NOTFOUND right after the FETCH and so falls into the trap of not processing the result set's loose change. I discovered this behaviour the hard way: investigating a bug which was caused by this precise problem. Did my original unit test fixture use a set of data which returned an exact multiple of the LIMIT clause? Yes it did. Should that data set have contained an irregular number of rows? Of course it should have!