Oracle- Bulk update data with commit frequency

Question

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.

Answer 1

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:

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