Thanks for the advice.

Yes, I was using a for update in my cursor. I wanted to mark the
successfully processed records so that I could delete them all in one go at
the end of the procedure. I suppose the way round this is to delete them as
I go (once I know processing have been successful) and issue a commit every
x number of records.

Paul

"Andy Hassall" <andy@andyh.co.uk> wrote in message
news:50r5f21jl0sdln9sesov1mk89t7d145c33@4ax.com...
> 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