Re: Problems with PL/SQL

From: Klaus Zeuch <KZeuch_at_nospamhotmail.com>
Date: Tue, 15 Aug 2000 21:12:09 +0200
Message-ID: <8nc4lb$7f5$15$1@news.t-online.com>

Hi,

First: Rownum only works in combination with the < operator; using rownum with the between-operator will return no hits (simply try as user scott: select * from emp where rownum between 2 and 3 -> no hits)

Suggestion:

declare
begin

   for v_c1 in (select * from table_source) loop

      insert into table_dest values (v_c1.column_name1, v_c1.column_name2,.....);

      if mod(v_c1%rowcount,1000) = 0 then
         commit;
      end if;

   end loop;
   commit;
end;

Klaus
"Andrei Romazanov" <romazanov.andrei_at_gfos.de> schrieb im Newsbeitrag news:8nc09i$lso$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:12:09 CDT