Oracle- Bulk update data with commit frequency

I want to update and insert millions of data via stored procedure in oracle. I have used below approach-

Fetching all the data in a list. Iterating through list and updating the data using commit frequency variable. If commit count reaches to commit frequency, I am commuting the queries. Let’s take example, my commit frequency is 10000, then I am committing when executed commit count reaches to commit frequency. Now let’s take example, if I am having 98000 rows to update and I am committing after every 10000 rows.

This will work for first 90000 rows so how to commit data for last 8000 rows ?

I want solution how to commit data which is not matching commit condition.


  • As you're iterating through the list, I presume you're doing it in a loop and have a commit as the last command before end loop. So, have yet another commit after it.

    Something like this:

      -- do whatever you do here
      l_counter := l_counter + 1;
      if l_counter = 10000 then
         commit;               --> this is what you have now
         l_counter := 0;
      end if;
    end loop;
    commit;                    --> add this