I wonder what is the difference between using a bulk collect and then loop on the result and using a cursor. And subquestion : Are there cases where the only option is a cursor? Thanks
Oracle implicitly optimizes PL/SQL for-cursor-loop to use fetches by 100 rows, so it's similar to bulk collect limit 100.
Simple example:
SQL> begin
2 for r in (select/*+findme*/ level n from dual connect by level<=100) loop
3 exit;
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select fetches, rows_processed,sql_text
2 from v$sql
3 where lower(sql_text) like 'select/*+findme*/%';
FETCHES ROWS_PROCESSED SQL_TEXT
---------- -------------- ----------------------------------------------------------
1 100 SELECT/*+findme*/ LEVEL N FROM DUAL CONNECT BY LEVEL<=100
1 row selected.
NB: Such optimisation works only if plsql_optmize_level
parameter>=2 (default=2). In case of plsql_optmize_level
< 2, for-loop fetches by 1 row. You can try to change it and check the difference.
PS. + You can't use a cursor for-loop with dynamic sql