Well, I disagree with his examples - but not the gist.

His examples are the form of:

get some rows
process them one by one


Now, if it were:

get some rows
process them
do a bulk update back into database

Then I would say "bulk bind".


The implicit cursor for loop implicitly array fetches 100 at a time - as you can see from his numbers - 100 was the 'sweet spot' (it is in general, the good number to use for this sort of stuff). So, if you are just fetching and processing the rows - a row at a time, just use the cursor for loop.

Here is an example of when I would not explicitly bulk bind:

ops$tkyte%ORA10GR2> create table t as select * from all_users where rownum <= 10;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure process( p_str in out varchar2 )
  2  is
  3  begin
  4          p_str := lower(p_str);
  5  end;
  6  /

Procedure created.

ops$tkyte%ORA10GR2><b>
ops$tkyte%ORA10GR2> begin
  2          for x in (select username from t)
  3          loop
  4                  process( x.username );
  5          end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed. </b>

why complicate that - there would be no benefit. Now, on the other hand, if I called PROCESS and then put the data back (process in this case would have to be something a LOT more complex than what I did above - in this example, we should not even be using plsql - but assume process is really complex procedural code and is necessary ) then I would, for example:

<b>
ops$tkyte%ORA10GR2> declare
  2      l_rowids    sys.odciridlist;
  3      l_usernames sys.odcivarchar2list;
  4      cursor c is select rowid, username from t;
  5      l_limit     number := 100;
  6  begin
  7      open c;
  8      loop
  9          fetch c bulk collect into l_rowids, l_usernames limit l_limit;
 10          exit when ( l_rowids.count = 0 );
 11
 12          for i in 1 .. l_rowids.count
 13          loop
 14              process( l_usernames(i) );
 15          end loop;
 16
 17          forall i in 1 .. l_rowids.count
 18              update t set username = l_usernames(i) where rowid = l_rowids(i);
 19      end loop;
 20      close c;
 21  end;
 22  /

PL/SQL procedure successfully completed.
 </b>

Now it makes sense to do it explictly - because we get N rows, process them all, and then FORALL update them (bulk update)

If you take the forall away - remove the need to update - I would revert back to my prior example (lots easier to code, read, understand, maintain, debug whatever)


So, I stick with the for x in (select) construct unless I have an opportunity to forall i bulk update/insert/delete - then I would implicitly array fetch - so as to have the data in an array, process it, and bulk it back in.