Commiting limited no of rows at a time

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!

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;
/

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;

Quote:

---

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

Thanks Guys! It worked.

Quote:

---

Originally posted by chrisrlong

Quote:

---

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

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

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

- Chris