Re: Problems with PL/SQL

Try this:

DECLARE
a INTEGER;
b INTEGER;
CURSOR sel_stmt IS SELECT * FROM table_source; sel_val sel_stmt%ROWTYPE;
BEGIN
a:=1
b:=1
FOR a IN (<series out how many records total you want to insert>) LOOP OPEN sel_stmt;
FETCH sel_stmt INTO sel_val;
INSERT INTO table_target VALUES (sel_val.<column1>, sel_val.<column2>, etc.) IF b = 1000
THEN GOTO commit_logic
ELSE GOTO continue_logic
END IF;
<<commit_logic>>
COMMIT;
b:=1;
<<continue_logic>>
a:=a+1
b:=b+1
END LOOP;
CLOSE sel_stmt;
--commit the last batch
COMMIT;
END;
/

I may have some mistakes in there whipping it out, but the gist is you have to deal with the records one at a time within the loop.

"Andrei Romazanov" <romazanov.andrei_at_gfos.de> wrote in message 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:43:20 CDT