Commiting limited no of rows at a time

[BRAUK]

I am Selecting number of rows from a table and inserting into another table in a pl/sql block.I want to commit only limited number of rows at a time(say 2000).how can this be achieved.

As always Thanks!

[kmesser]

create or replace procedure myproc as
x number(4);
begin
loop
insert into newtable values( . . . );
x := x+1;
if mod(x,2000) = 0 then
___commit;
end if;
exit when . . .
end loop;
end;
/

[arun_rashmi]

create or replace procedure test
x number;
begin
insert into testtable......
select count(*) into x from testtable;
if mod(x,2000)=0 then commit;
end if;
end;

[chrisrlong]

Originally posted by arun_rashmi
select count(*) into x from testtable;

You *gotta* be kidding

Stick with Kmesser's solution!

Although I'd add that you should look into bulk processing (FORALL) which will really save you time in the loop.

- Chris

[BRAUK]

Thanks Guys! It worked.

[marist89]

Originally posted by chrisrlong

Originally posted by arun_rashmi
select count(*) into x from testtable;

You *gotta* be kidding

I think quitting smoking is giving you a little "jmodic" sense of humor...

[chrisrlong]

I *am* becoming a real PITA, aren't I?

I've either gotta quit this book ar take up smoking again - something's gotta give

- Chris