Prefetch is a new 8i feature whereby the client will automagically array fetch for you -- you don't have to do array fetching anymore, it'll just happen.

You use it the same way you would ANY of the options for. Typically it is used on the command line

$ proc ... prefetch=N ...

but you can embedded it in your program like this:

main( argc, argv )
int argc;
char * argv[];
{
exec sql begin declare section;
char oracleid[500]="scott/tiger";
int empno;
exec sql end declare section;


EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
EXEC SQL WHENEVER SQLWARNING DO sqlerror_hard();
EXEC SQL CONNECT :oracleid;
exec sql alter session set sql_trace=true;


exec sql declare c1 cursor for select empno from emp A;
exec sql declare c2 cursor for select empno from emp B;

exec oracle option( prefetch=5 );
exec sql open c1;
for( ;; )
{
exec sql whenever notfound do break;
exec sql fetch c1 into :empno;
}
exec sql close c1;

exec oracle option( prefetch=50 );

exec sql open c2;
for( ;; )
{
exec sql whenever notfound do break;
exec sql fetch c2 into :empno;
}
exec sql close c2;

exec sql commit work release;
}

and then to verify it worked -- I'll run tkprof on the trace file:


select empno
from
emp A


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 0 3 4 15
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.00 0 3 4 15

select empno
from
emp B


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 1 4 15
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 1 4 15

and sure enough -- the first cursor was actually fetched from 3 times for 15 rows and the second one fetched from only once since the array size was changed.