Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: Problems with PL/SQL
Sorry,
I forgot: cursor-attributes like %rowcount are not available in FOR <record>
IN <SELECT-Statement> loops;
use a counter inside the loop and use the mod-function against this counter:
either
declare
v_count number := 0;
begin
for v_c1 in (select * from table_source) loop
insert into table_dest values (v_c1.column_name1, v_c1.column_name2,.....);
v_count := v_count + 1; if mod(v_count,1000) = 0 then commit; end if;
Klaus
"Klaus Zeuch" <KZeuch_at_nospamhotmail.com> schrieb im Newsbeitrag
news:8nc4lb$7f5$15$1_at_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:32:42 CDT