Re: OPEN CURSORS AND COMMIT

On Mon, 13 Sep 1999 15:45:54 -0500, Kent Eilers <kent.eilers_at_pca.state.mn.us> wrote:

>I have some cursors I use to populate some insert SQL statements. I am
>not using the 'FOR UPDATE' clause.
>
>I get the invalid fetch error when I attempt to do a rollback.
>
>How can I use cursors in my PL/SQL code with commits and rollbacks. I
>certainly do not want to have to close any cursors .
>

I have seen this problem under the following circumstances:

1. Issue an update statement
2. Execute code like: for each r in cursor loop begin update ... if <condition> raise ErrorException ... commit; exception when ErrorException then rollback ... end; end loop;

If the error exception is raised on the first row and a rollback takes place, you will subsequently get a fetch out of sequence error. This occurs because the rollback rolls back the update statement in step 1. The cursor opened in step 2 is then invalid, because it refers to a database context that no longer exists. Solutions include commiting before the start of the loop or setting a savepoint at the start of the loop and rollback to savepoint in the exception handler. Neither solution is particularly good. Received on Sun Sep 19 1999 - 07:04:13 CDT