drop table my_parts 

create table my_parts (partnum number, partname varchar2 (100), quantity_sold number(2)) 

drop type my_parts_nt force 

drop type my_parts_ot force 

create or replace type my_parts_ot authid definer is object 
   (partnum number, partname varchar2 (100), quantity_sold number(20)) 

create or replace type my_parts_nt is table of my_parts_ot; 

begin 
   for i in 1..99 loop 
      insert into my_parts values ( 
         i, 
         'part ' || i, 
         i 
      ); 
   end loop; 
    
   commit; 
end; 

create or replace procedure show_count 
   authid definer 
is 
   n number; 
begin 
   select count(*) 
     into n 
     from my_parts; 
   dbms_output.put_line('rows in my_parts: ' || n); 
end; 

create or replace procedure insert_n ( 
   p_n in number 
) 
   authid definer 
is 
begin 
    show_count; 
    insert into my_parts ( 
      partnum, 
      partname, 
      quantity_sold 
   ) 
      select partnum * partnum, 
             'new  ' || partname, 
             partnum + partnum 
        from my_parts 
       where partnum <= p_n and partname not like 'new%'; 
   dbms_output.put_line('inserted ' 
                        || sql%rowcount 
                        || ' rows'); 
   show_count; 
exception 
   when others then 
      dbms_output.put_line('ERROR!'); 
      dbms_output.put_line('inserted ' 
                           || sql%rowcount 
                           || ' rows'); 
      show_count; 
      dbms_output.put_line('error: ' || sqlerrm); 
end; 

begin 
   insert_n(10); 
   insert_n(49); 
   insert_n(50); 
   insert_n(100); 
   rollback; 
end; 

create or replace procedure insert_n_forall ( 
   p_n in number 
) 
   authid definer 
is 
   l_parts my_parts_nt; 
begin 
   select my_parts_ot (partnum * partnum, 
          'new  ' || partname, 
          partnum + partnum) 
   bulk collect 
     into l_parts 
     from my_parts 
    where partnum <= p_n 
      and partname not like 'new%'; 
                         
   forall indx in 1..l_parts.count 
   save exceptions 
      insert into my_parts ( 
         partnum, 
         partname, 
         quantity_sold 
      ) values ( 
         l_parts(indx).partnum, 
         l_parts(indx).partname, 
         l_parts(indx).quantity_sold 
      ); 
   dbms_output.put_line('inserted ' 
                        || sql%rowcount 
                        || ' rows'); 
   show_count; 
exception 
   when others then 
      dbms_output.put_line('ERROR! ' ); 
      dbms_output.put_line('inserted ' 
                           || sql%rowcount 
                           || ' rows'); 
      show_count; 
      dbms_output.put_line('error: ' || sqlerrm); 
end; 

begin 
   insert_n_forall(10); 
   insert_n_forall(49); 
   insert_n_forall(50); 
   insert_n_forall(100); 
   rollback; 
end;