update in bulk
hi how can i update records within one am having 6 million records to update, i try this am in 12c database
DECLARE c_limit PLS_INTEGER := 100; CURSOR employees_cur IS SELECT employee_id FROM employees WHERE department_id = department_id_in; TYPE employee_ids_t IS TABLE OF employees.employee_id%TYPE; l_employee_ids employee_ids_t; BEGIN OPEN employees_cur; LOOP FETCH employees_cur BULK COLLECT INTO l_employee_ids LIMIT c_limit; -- This will make sure that every iteration has 100 records selected EXIT WHEN l_employee_ids.COUNT = 0; FORALL indx IN 1 .. l_employee_ids.COUNT SAVE EXCEPTIONS UPDATE employees emp -- Updating 100 records at 1 go. SET emp.salary = emp.salary + emp.salary * increase_pct_in WHERE emp.employee_id = l_employee_ids(indx); commit; FORALL indx IN 1 .. l_employee_ids.COUNT SAVE EXCEPTIONS UPDATE employees_details emp -- Updating 100 records at 1 go. SET emp.salary = emp.salary + emp.salary * increase_pct_in WHERE emp.employee_id = l_employee_ids(indx); commit; END LOOP; EXCEPTION WHEN OTHERS THEN IF SQLCODE = -24381 THEN FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP -- Caputring errors occured during update DBMS_OUTPUT.put_line ( SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX || ‘: ‘ || SQL%BULK_EXCEPTIONS (indx).ERROR_CODE); --<You can inset the error records to a table here> END LOOP; ELSE RAISE; END IF; END;
