Limiting Bulk Collect

We run Oracle 8i EE on Sun Solaris 2.6 and on NT 4.0 SP5.

Bulk collect into works fine but I don't know how to limit the number of rows to retrieve in one go. I do roughly

type t1 is table of integer index by binary_integer; b1 t1;
type t2 is table of number index by binary_integer; b2 t2;
type t3 is table of date index by binary_integer; b3 t3;
...

open c;
fetch c bulk collect into b1, b2, b3;
close c;

where b1, b2, b3 are tables of xxxx index by binary_integer. If I pass this to ADO in an ASP page I get ORA-06513 when I return more than was expected.

I can limit the amount of fetched rows by using VARRAY (nnn) of xxx, but I cannot code nnn fixed as it is only known at runtime. For this I did

type t1 is varray (999) of integer;
b1 t1;
type t2 is varray (999) of number;
b2 t2;
type t3 is varray (999) of date;
b3 t3;
end_of_bulk exception;
pragma exception_init (end_of_bulk, - 22160);
...

open c;
begin

   fetch c bulk collect into b1, b2, b3; exception

   when end_of_bulk then
   null;
   when others then
   raise;
end;
close c;

Can you perhaps help me? Any hints appreciated.

Martin Received on Thu Jan 13 2000 - 16:18:04 CST