Re: ORA-01002 Fetch out of sequence

Paul Simmons wrote in message <373781CD.8A25F779_at_death.iinet.net.au>...

[snip]

>
>More to the point it is good to remember that any commit before a cursor
>is closed will close the cursor(s)
>
>

No, that's not correct. The cursor remains open after a COMMIT/ROLLBACK. A COMMIT makes changes permanent, then releases row locks. A ROLLBACK undoes changes, then releases row locks. Period.

When using FOR UPDATE, the RDBMS guarantees that fetched rows are already locked by your session. When the locks are released, the RDBMS can no longer keep this guarantee, hence giving you a 'Fetch out of sequence' error.

A local cursor (declared in an anonymous block, a procedure or a function) is closed when excplicitly CLOSEd or else when the block/procedure/function in which is was declared terminates.

A package cursor is closed when explicitly CLOSEd or else when the session terminates.

[snip]

>
>Hence this is commiting your update and closing the cursor, next fetch
>can not be achieved because you have closed the cursor, hence the error.
>

Again, not correct. COMMIT does not implicitly close the cursor.

[snip]

Two guidelines:
1) CLOSE your cursors explicitly - always! and as soon as you don't need them (Open cursors take up memory).
2) Don't COMMIT/ROLLBACK when FOR UPDATE cursors are open - CLOSE them first.

Note that row locks are still effective after the cursor is closed. Row locks are *only* released on:

1) COMMIT
2) ROLLBACK
3) Session termination

Regards,

Roy Brokvam
roy.brokvam_at_conax.com Received on Tue May 11 1999 - 01:13:46 CDT