Re: Problems with PL/SQL

"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
>

Change the select into a cursor:
declare
v_count := v_count + 1 ;
cursor c_source is
  select * from source_table ;
begin
open c_source ;
loop
  fetch c_source into .....
  exit when c_source%notfound ;
  v_counter := v_counter + 1 ;
  if mod(v_counter,1000) = 0 then commit ; end loop ;
close c_source ;
end ;
/
This is live typing so beware of typo's.

• Mark D. Powell -- The only advice that counts is the advice that you follow so follow your own advice. --

  ---

Got questions? Get answers over the phone at Keen.com. Up to 100 minutes free!
http://www.keen.com Received on Tue Aug 15 2000 - 14:52:45 CDT