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