Re: Fetch out of sequence in cursor

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