Tuesday 13 March 2012

How to use Bulk collect.

declare
  2      type r262 is table of t262%rowtype;
  3      l_data r262;
  4      cursor c_data is
  5          select * from t262;
  6      n pls_integer := 0;
  7  begin
  8      open c_data;
  9      loop
 10          fetch c_data bulk collect into l_data limit 100;
 11          dbms_output.put_line('rowcount='||l_data.count());
 12          exit when c_data%notfound;                                   -----------this is modified see the below one
 13          for i in l_data.first()..l_data.last() loop
 14              n := n+1;
 15          end loop;
 16      end loop;   
 17      dbms_output.put_line('total rows processed ='||n);  
 18      close c_data;
 19  end;
 20  /

As above if we mention the 100 then it will be problem in case of 2222. How means? It will run 100 for 22 times and after that it will check for 100 more records but the exists records are 22. So, it will not process those 22 records that means we are losing the data. So, to overcome this below updated one

 declare
  2      type r1000 is table of t262%rowtype;
  3      l_data r1000;
  4      cursor c_data is
  5          select * from t262;
  6      n pls_integer := 0;
  7  begin
  8      open c_data;
  9      loop
 10          fetch c_data bulk collect into l_data limit 100;
 11          dbms_output.put_line('rowcount='||l_data.count());
 12          exit when l_data.count() = 0;                                    ---------this is the modified one.
 13          for i in l_data.first()..l_data.last() loop
 14              n := n+1;
 15          end loop;
 16      end loop;
 17      dbms_output.put_line('total rows processed ='||n);  
 18      close c_data;
 19  end;
 20  /

No comments:

Post a Comment