Re: Error ORA-01002

In article <340D39B4.28FE_at_msi.mcc.es>, Alberto Benavente <industria.andoain_at_msi.mcc.es> writes
>I get an error ORA-01002 when I do the following
 [snip]
>
>Procedure PRC_PRUEBA IS
>
> CURSOR cEmp IS
> select 'X'
> from emp ;
>
> vBasura VARCHAR2(1) ;
> vContador NUMBER(3) := 1 ;
>
>BEGIN -- Procedure PRC_PRUEBA
>
> insert into prueba values (0) ;
>
> OPEN cEmp ;
> LOOP
> FETCH cEmp INTO vBasura ;
> EXIT WHEN cEmp%NOTFOUND ;
>
> insert into prueba values (vContador) ;
>
> IF MOD (vContador, 2) <> 0 THEN
> ROLLBACK ;
> ELSE
> COMMIT ;
> END IF ;
>
> vContador := vContador + 1 ;
> END LOOP;
> CLOSE cEmp ;
>
>END; -- Procedure PRC_PRUEBA
>

 [snip]
>
>· And I get this error
>
>ORA-01002: fetch out of sequence
>ORA-06512: at "SCOTT.PRC_PRUEBA", line 16
>ORA-06512: at line 2
>
>
>Oracle Support says than I can't do COMMIT and ROLLBACK
>inside a cursor. What about this?

It's true. When you perform COMMIT or ROLLBACK you loose the cursor. You would need to re-open the cursor to make further use of it.

Either don't commit/rollback until the end of the procedure or include some update to the cursor so that subsequent re-opens do not pull back the same data.

Andy

-- 
Andy Hardy