Re: NG for ORACLE PL/SQL?

In article <8nc06a$lm9$1_at_news.germany.net>,   "Andrei Romazanov" <romazanov.andrei_at_gfos.de> wrote:
> Hi,
>
> what NG can help me by common ORACLE PL/SQL-Problems? For example I
 want to
> insert from a large table with commit after 1000 records. My first
 idea
> wasn't correct :-(
>
> declare
> a integer;
> b integer;
> begin
> b:=10;
> for a in 1..1000 loop
> INSERT INTO table_dest SELECT * FROM table_source WHERE rownum
 between b
> and b + 1000;
> commit;
> b:=b+1000;
> end loop;
> end;
> /
>
> Thanks
>
> Andrei
>

I would use a cursor and a counter variable for this. Try something like this...

declare
cursor c1 is select ... from table;
r1 c1%rowtype;
commit_counter number;

begin
commit_counter := 0;
open c1;
loop
fetch c1 into r1;
exit when c1%notfound;

insert into table values (r1.column, etc... );

commit_counter := commit_counter + 1;

if commit_counter > 1000 then
commit;
commit_counter := 0;
end if;

end loop;
close c1;
end;

alternatively you can simply assign a large rollback segment to the transaction such that the entire insert...select can be handled in one shot

Hope that helps,

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Aug 15 2000 - 18:44:06 CDT