I've been asked to create a procedure which should delete from a table records which are older than a specified period(input parameter) with a User specified Commit Frequency(This will be another Input parameter)
I have a column called created_time in mytable which i could use in WHERE clause of the DELETE statement.
create or replace procedure delete_mytable
(
p_commitfreq in number
p_no_of_days_old in number
)
as
begin
DELETE FROM mytable where created_time< sysdate-p_no_of_days_old;
-- code to implement COMMIT frequency
end;
How can i implement COMMIT frequency in this proc?. Client is going to COMMIT after every 35000 rows. But they don't want this to be hard coded. I have seen other threads in OTN where Gurus are saying that including COMMIT frequency is a bad thing. This just happens to be in SPECS which I don't get to design(we are actually converting a Purge script written in C++ to PL/SQL)