Re: PL/SQL loop problem

There are a few issues with what you are doing.

1. As coded in your post, and as Daniel has already pointed out, you will get an endless loop. Though I suspect you have an EXIT clause after the cursor%NOTFOUND in you actual code, otherwise you would have hit the snag very quickly. [Quoted]
2. You can not have a commit or rollback within a "cursor for update" loop. This is commonly called "commit across fetches". A cursor for update, which I think was what you used, will lock the rows that will be returned. A commit or rollback will terminate the transaction and release the lock on those rows. When you next do a fetch, you will get an ORA-1002 "outof sequence" error.
3. It is not a good idea to have a commit within a cursor loop in any case. People sometime do it because of the rollback segment usage concern. Hopefully the undo tbs in 9i can alliviate some of the problem.
4. It is not a good idea to have a commit or rollback in a stored procedure - unless you have autonomous transaction withn the procedure. This type of coding makes transaction managemnet more difficult. It is better to control the transaction from outside of the procedure.
5. It is not clear to me how you do the update on the last record of the result set. You are updating the record "of the table" are you not? The way you coded you can not use the WHERE CURRENT OF clause. So you will have to depend on your knowledge of the data to do the update. There is no clean-cut way of identifying the last row of a result set. Of course it can be done but I will not comment further until I see your full codes and know exactly how you are doing the update.

Gaggl Paul <Paul.Gaggl_at_wifo.ac.at> wrote in message news:<3D57B523.63BA46D9_at_wifo.ac.at>...
> Hey!
>
> I have a loop structure like the following:
>
> open cursor;
> loop
> fetch cursor into line;
> if cursor%NOTFOUND
> then
> update
> end if;
>
> if
> update
> elsif
> update
> end if;
>
> end loop;
>
> As you can see i want to update the last record twice if one of the if
> conditions
> is true. The problem now is, where to put the commit statement. If i put
> the commit
> statement outside the loop the last update is "left out" because the
> modified record
> can not be found, because it has not yet been modified.
> If i put the commit statement inside the loop i get a "cursor out of
> sequence" exception.
>
> Has anyone of you an idea what to do here?
>
> Paul
> --
>
>
>
> ?terreichisches Institut f? Wirtschaftsforschung WIFO
>
> Name: Paul Gaggl Postadresse: Postfach 91
> Tel.: +43-1-7982601-229 A-1103 Wien
>
> Fax: +43-1-7989386 Standort: Arsenal Objekt 20
> Mail: gaggl_at_wifo.ac.at A-1030 Wien
>
>
> http://www.wifo.ac.at/
Received on Tue Aug 13 2002 - 05:55:09 CEST