Delete from...Commit redux

From: Ronald L Misiorowski <misioror_at_GAS.UUG.Arizona.EDU>
Date: 1997/05/12
Message-ID: <5l7qa9$fv4$1_at_news.ccit.arizona.edu>#1/1

[Quoted] Many thanks to all of you who pointed out the rookie ENDIF error.

[Quoted] [Quoted] 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

[Quoted] 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;
[Quoted]    IF counter > 100

      THEN 
	COMMIT;
[Quoted]         counter := 0;     <--------- this is line 17

   END IF;
  END LOOP;
END; I can delete records ok from sql plus prompt as such:

delete from mem_month_by_period
where period = '01-JAN-97'
and months <= 0;

and it works just fine--deleting 216,000 rows with no ORA-01562...

Anybody got any ideas on why my cursor-driven code doesn't work? It's like Oracle never sees the COMMIT statement!

Thanks for any and all help!

John Dunn
The University Physicians Received on Mon May 12 1997 - 00:00:00 CEST