Re: Delete from...Commit redux

Andreas Baumkötter wrote:
>
> misioror_at_GAS.UUG.Arizona.EDU (Ronald L Misiorowski) wrote:
>
> >Many thanks to all of you who pointed out the rookie ENDIF error.
 

> >The code now works (no more syntax errors) but I'm getting the dreaded
> >ORA-01562: failed to extend rollback segment number 1
> >ORA-01650: unable to extend rollback segment R02 by 512 in tablespace RBS
> >ORA-06512: at line 17
 

> >Here's what I've got now:
 

> >DECLARE
> >CURSOR test1
> >IS
> >SELECT rowid
> >FROM mem_month_by_period
> >WHERE months <= 0;
> >test_cursor_row test1%ROWTYPE;
> >counter INTEGER := 0;
> >BEGIN
> > FOR mem_months_by_period IN test1
> > LOOP
> > counter := counter + 1;
> > delete mem_month_by_period;
> > IF counter > 100
> > THEN
> > COMMIT;
> > counter := 0; <--------- this is line 17
> > END IF;
> > END LOOP;
> >END;
>
> ORACLE PL/SQL User's Guide and Reference, Page 4-38:
>
> "Also, the cursor must have a read-consistent view of the data, so
> rollback segments used in the update are not released until the
> cursor is closed."
>
> The solution is to divide the cursor into a number of smaller cursors,
> e.g. to select with ROWNUM < X and to build an outer loop.
>
> Hope this helps.
>
> Ciao, Andreas
> -
> Andreas Baumkoetter, PRO DV Software GmbH, Dortmund, Germany
> -> PGP public key available <-

You are deleting all the records every time in the loop. The code should be as follows
 delete mem_month_by_period
 where rowid = mem_months_by_period.rowid ;

When you are trying to delete all the records, Oracle tries writting it into rollback segment and running out of space. Received on Wed May 14 1997 - 00:00:00 CEST