Tuesday, January 02, 2007

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!

7 Comments:

Blogger Tim... said...

Good heads-up there!

Just shows how easy it is to hide problems when you strip examples down too much.

Cheers

Tim...

2 January 2007 at 04:45:00 GMT-8  
Anonymous Anonymous said...

Well, the 10g Documentation is not too verbose on the subject, but it gives at least correct example with comments, which may point to the pitfall. http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14261/tuning.htm#sthref2231

Best regards

Maxim

2 January 2007 at 06:32:00 GMT-8  
Blogger APC said...

Maxim

Thanks for catching that. I did intend to check the 10g documentation but I guess I forgot in all the excitement.

Cheers, APC

4 January 2007 at 02:52:00 GMT-8  
Anonymous Anonymous said...

I could not reproduce the second error.
declare
type test_t is table of test%rowtype;
v_test_t test_t;
cursor cur_1
is select * from test;
n pls_integer := 0;
begin
open cur_1;
loop
fetch cur_1 bulk collect into v_test_t limit 100;
dbms_output.put_line('rowcount =' || v_test_t.count());
for i in v_test_t.first()..v_test_t.last()
Loop
n:=n+1;
end loop;
exit when cur_1%notfound;
end loop;
dbms_output.put_line('total rows processed=' || n);
close cur_1;
end;
/

SQL> @ex1
rowcount =100
rowcount =100
rowcount =100
rowcount =100
rowcount =100
rowcount =100
rowcount =100
rowcount =100
rowcount =35
total rows processed=835

PL/SQL procedure successfully completed.

4 January 2007 at 09:45:00 GMT-8  
Anonymous Anonymous said...

ignore my post. Missed the exact multiple of LIMIT clause.

4 January 2007 at 09:47:00 GMT-8  
Anonymous Anonymous said...

APC,

Because you're bulk collecting, you should ideally use 1 .. array.COUNT as your FOR LOOP boundaries rather than array.FIRST .. array.LAST. Then you remove the error and can use %NOTFOUND.

Although having said that, I've been using the EXIT WHEN array.COUNT = 0 for years because I'm a FIRST..LAST guy!

Cheers

5 January 2007 at 05:42:00 GMT-8  
Anonymous Anonymous said...

Fantastic, I have had this particular issue regularly, and tried debugging in every possible way I could think of.
Tried your way, and haven't received it since.

Thanks for doing the hard work ;0)

20 March 2008 at 13:27:00 GMT-7  

Post a Comment

<< Home