On Mon, 28 Aug 2006 10:26:47 GMT, "PAUL MADDSION" <60_pm@blueyonder.co.uk>
wrote:

>I have a cursor which loops through a table and does some processing.
>Towards the end of the loop I update another table and I want to COMMIT each
>record in turn. I'm getting a fetch out of sequence message when i try to
>run this and believe it is because I'm trying to commit within the cursor
>loop. Any suggestions on how I can get round this would be greatly
>appreciated. An outline of what I'm trying to do is shown below.
>
>PROCEDURE update_rec
>IS
>--
>CURSOR c_record ...
>--
>processed_rec c_record%ROWTYPE;
>--
>BEGIN
> FOR r_record IN c_record LOOP
>--
> BEGIN
....
>-- if I get this far I want to commit individual records
> COMMIT;
> EXCEPTION
> ROLLBACK;
> handle any exceptions raised in the loop
> END LOOP;

The commit in the loop is dodgy practice. But it could be the rollback that's
finishing you off:

http://www.oracle.com/technology/ora...o54asktom.html

Or you're using FOR UPDATE in which case it'll fail straight away.

--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool