Re: NG for ORACLE PL/SQL?

IMO, .server is the proper newsgroup for pl/sql questions.

In your piece of code I wouldn't bother about rownums. What I would do is

declare i integer := 0;
begin
for rec in (select * from table_source) loop insert into table_dest
values (rec.<col1>, etc...)
i := i + 1;
if i > 1000 then
  commit;
  i := 0;
end if;
end loop;
end;
/
The other solution of course is making sure your rollback segments are big enough to be capable of dealing with the complete table.

Hth,

Sybrand Bakker, Oracle DBA

"Andrei Romazanov" <romazanov.andrei_at_gfos.de> wrote in message news:8nc06a$lm9$1_at_news.germany.net...
> 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
>
>
Received on Tue Aug 15 2000 - 14:02:14 CDT