Bulk update with commit in oracle

Question

I am performing bulk update operation for a record of 1 million records. I need to COMMIT in between every 5000 records how can I perform?

update tab1 t1
   set (col1,col2,col3,col4)= 
   (select col1,col2,col3,col4 from tab_m where row_id= t1.row_id);

Answer 1

If you are looking for a solution in PLSQL you can do it by using BULK INSERT/UPDATE as below:

  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;    
      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;