Re: PL/SQL loop problem

Gaggl Paul wrote:

> 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
> --
> ___________________________________________________________________
>
> Österreichisches Institut für 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/

Before you worry about where to put the commit ... worry about how to get out of an endless loop.

Do this:

open cursor;
loop

    fetch cursor into line;

--    if cursor%NOTFOUND
--    then
--        update
--    end if;

   EXIT WHEN cursor%NOTFOUND;

    if

        update
    elsif

        update
    end if;

end loop;
COMMIT;
CLOSE CURSOR An update when cursor%NOTFOUND will do something ... perhaps even something you want ... but it won't get you out of the loop. Ever!

Daniel Morgan Received on Mon Aug 12 2002 - 17:34:36 CEST