I want to commit records for every 1000 records updated.
set serveroutput on;
declare
commit_count NUMBER(20):=0;
begin
for rec in
(
select LOC_ID from customer_mast WHERE SFflag IS NULL
)
LOOP
UPDATE customer_hist SET SFflag='Y' WHERE LOC_ID=rec.LOC_ID;
commit_count:=SQL%rowcount;
Dbms_Output.Put_Line('commit_count ' || commit_count );
If MOD(commit_count,1000)=0 THEN
COMMIT;
END IF;
END LOOP;
Exception
When Others Then
Dbms_Output.Put_Line(' ERROR '||SQLERRM);
End;
If MOD(commit_count,1000)=0 THEN
COMMIT;
END IF;
This is not working as for each LOC_ID the number of records are not equal to 1000.
The counts are as below
commit_count 1567
commit_count 9876
commit_count 3456
commit_count 4345
commit_count 9698
commit_count 972
How to commit records for every 1000 records updated.
Please help me
Thanks in advance.