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;