Re: %rowcount

On 19 May 2006 09:09:47 -0700, esokol_at_cerner.com wrote:

>Does anyone know of any documentation which exists about changes
>between 7.3.4 and 8.1.7.4 related to %rowcount?
>
>We have the following code which behaves differently on 8.1.7.4. On
>7.3.4 the FETCH appears to put data into (1) for the first fetch but in
>8.1.74 it appears to put it in (0).
>
>I know the documentation says that ROWCOUNT is 1 after the first fetch.
> The question is whether during the "fetch" it is zero or 1. It
>appears that in 7.3.4 it was 1 and that 8.1.7.4 it is actually still
>zero.
>
>Any pointers to documentation about this or your experience would be
>appreciated.
>
> OPEN C_2;
> LOOP
> FETCH C_2 INTO
> MATCH.CLASS_1 (C_2%ROWCOUNT),
> MATCH.PROCEDURE_ID_3 (C_2%ROWCOUNT),
> MATCH.COMPLETED_4 (C_2%ROWCOUNT),
> MATCH.ORDER_ID_10 (C_2%ROWCOUNT),
> MATCH.ACCOUNT_NUMBER_11 (C_2%ROWCOUNT),
> MATCH.MRN_12 (C_2%ROWCOUNT),
> MATCH.P2_14 (C_2%ROWCOUNT),
> MATCH.PATIENT_15 (C_2%ROWCOUNT),
> MATCH.ACCOUNT_16 (C_2%ROWCOUNT),
> MATCH.ORDER_17 (C_2%ROWCOUNT)
> ;
> EXIT WHEN C_2%NOTFOUND;
>
> CNT := C_2%ROWCOUNT;
>
> END LOOP;
> CLOSE C_2;
Oracle 8i has BULK COLLECT, so you can
FETCH .. BULK COLLECT INTO ... LIMIT <n>

I would recommend using that and forget about the disastrous code above. The replacement will perform a proper array fetch, so will be much faster.

--
Sybrand Bakker, Senior Oracle DBA