Wednesday, 12 October 2016

PL/SQL - Commit in batches test

for z in SED
do
sqlplus /nolog<<END
connect JUSTIN/JUSTIN
set pages 100 lines 220
drop table sed_flip_1;
create table sed_flip_1 ( x int );
set serveroutput on
declare
 CNT number := 0;
begin
    for i in 1 .. 500001
    loop
        insert into sed_flip_1 values (i);
        CNT := CNT + 1;
        if CNT = 1000 then
        commit work write immediate wait;
 dbms_output.put_line('Committing now...'||CNT||' '||i);
        CNT := 0;
        end if;
    end loop;
end;
/
-- truncate table justin.sed_flip_1;
delete from justin.sed_flip_1;
disconnect
END
done