Re: Fetch out of sequence in cursor

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_at_andyh.co.uk> wrote in message news:50r5f21jl0sdln9sesov1mk89t7d145c33_at_4ax.com...
> On Mon, 28 Aug 2006 10:26:47 GMT, "PAUL MADDSION" <60_pm_at_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/oramag/oracle/04-sep/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
Received on Mon Aug 28 2006 - 17:05:08 CDT