2020-08-06 11:08:00
Topic starter
I have a oracle table called MY_TABLE and want to delete it in 1% chunks with a commit after 1% is deleted. I also want to give user output after every 1% is deleted. How do you do this in PLSQL?
The following PL/SQL block will run an update statement on batches sized at 10,000. Each batch will report a message of completion.
DECLARE
batch_size NUMBER := 10000;
cnt NUMBER := 1;
BEGIN
-- WHILE (TRUE) can be omitted: "loop ... end loop;"
-- already is an endless loop
LOOP
update partner set ent_name='a' where ent_name='b'
AND rownum <= batch_size;
exit when sql%notfound; -- notice that exit accepts "when condition"
--IF sql%notfound THEN -- you can avoid a if/endif by using "exit when"
-- EXIT;
-- END IF;
dbms_output.put_line('Partner: Ent Name Batch: #'||cnt);
cnt := cnt + 1;
COMMIT;
END LOOP;
commit; -- you missed this commit for the last iteration
END;
/