Re: Delete from...Commit redux

[Quoted] 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 <- Received on Tue May 13 1997 - 00:00:00 CEST