Friends...
DB: 11gr2
OS: linux
Table: EMPLOYEES (partitioned table with 50million rows)
I have searched through many threads for suggestions but most are using cursor and in others experts are suggesting not to commit in between update...
But I don't see any other option and also if update fails I can always rerun procedure...
Question:
1. How to write procedure to update table and commit after every 5000 rows?
2. I will not have idea how many rows from employees table partition need to be update so can't use 1..5000, since this will hang procedure after 5000 records.
I have written below procedure but it's not working
CREATE OR REPLACE PROCEDURE update1 (T1 in number, T2 in number) AS
for i in 1..5000
loop
UPDATE employees
SET emp_address = 'NA'
WHERE emp_id >= T1
AND emp_id <= T2
AND rownum <=(i);
DBMS_OUTPUT.PUT_LINE(sql%rowcount||' records updated.');
if mod(i, 5000)=0 then
commit;
end if;
end loop;
end;
/
EXEC update1(101, 200);
Could somebody please look into debugging this?
Thanks,
Mike