Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

update in bulk

Tony007Nov 14 2019 — edited Nov 14 2019

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;

Comments
Post Details
Added on Nov 14 2019
9 comments
455 views