ORACLE: Batch Commits
I had to write an Oracle script template for work to kind of create a guideline for our developers since we are getting inputs from ourselves, DBA standards, and business requirements from our BA’s and customers.
Anyway, one thing they wanted was batch commits . . . so I wrote this quick little piece:
It creates a temp table, populates it, and puts a DBMS_OUTPUT line for whatever number I set the commit at . . . pretty self explanatory.
CREATE GLOBAL TEMPORARY TABLE temp_Cursor_Count (
testNumber Number(10)
) ON COMMIT PRESERVE ROWS;
commit;
DECLARE
curVariable Number;
cursorCount Number(10) := 0;
cursorCommit Number(10) := 1000;
cursorMod Number(10) := 1;
CURSOR tt is
select * from temp_Cursor_Count;
begin
–populate temp table
For i in 1..9999
Loop
insert into temp_Cursor_Count(testNumber) values (i);
end loop;
commit;
FOR record_tt IN tt LOOP
–INITIALIZE Cursor values
curVariable :=record_tt.testNumber;
cursorCount := cursorCount + 1;
select MOD(cursorCount,cursorCommit) into cursorMod from dual;
if cursorMod=0 then
–Normally this is where your COMMIT would occur.
DBMS_OUTPUT.PUT_LINE(cursorCount);
end if;
END LOOP;
end;
truncate table temp_Cursor_Count;
commit;
drop table temp_Cursor_Count;
commit;