CODEWITHSUNDEEP
sqloracle-databaseplsql
Thej
Thej

Reputation: 375

Bulk update with commit in oracle

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

Upvotes: 4

Views: 36898

Answers (2)

Avrajit Roy
Avrajit Roy

Reputation: 3303

Per th question, if you only want to continue updating even if record fails with error logging then i think you should go with the DML error logging clause of Oracle. Hope this helps.

BEGIN
  DBMS_ERRLOG.CREATE_ERROR_LOG('TAB1');
  UPDATE tab1 t1
  SET
    (
      COL1,
      COL2,
      COL3,
      COL4
    )
    =
    (SELECT COL1,COL2,COL3,COL4 FROM TAB_M WHERE ROW_ID= T1.ROW_ID
    ) LOG ERRORS REJECT LIMITED UNLIMITED;

END;

Upvotes: 3

XING
XING

Reputation: 9886

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;

Upvotes: 4

Related Questions