Deleting x records, then COMMIT

From: John L Dunn <misioror_at_gas.uug.arizona.edu>
Date: 1997/05/08
Message-ID: <Pine.SOL.3.91.970508100636.19067B-100000_at_helium.gas.uug.arizona.edu>#1/1

Greetings:

I've got a large (6 Million rows) table that I want to delete rows from. When I issue the delete command from SQL*Plus:

DELETE FROM table
WHERE ... I get a message indicating I've blown out my rollback segment space-- I'm probably asking for Oracle to delete 5 Mill rows at once! I thought I would create a short script to issue a COMMIT after x records to keep this from happening. I'm new to PL/SQL and need help with the construct.

Here's what I've got:

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;
ENDIF;
END LOOP;
END; When I try to execute this I get:

18> ENDIF;  
19> END LOOP;  
20> END;  
21> END LOOP;  

ORA-06550: line 19, column 6: PLS-00103: Encountered the symbol "LOOP" when expecting one of the following:

if

Now my questions:

1. Is this the right way to perform deletes of x rows of data?  
2. If so, can you help me with the code above?  
3. If not, can you point me to code that will work?  

Thanks alot for your help!

John Dunn
The University Physicians
email: jdunn_at_umcaz.edu (preferred) or misioror_at_gas.uug.arizona.edu

* Received on Thu May 08 1997 - 00:00:00 CEST