Difference between bulk collect and cursor

Question

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

Answer 1

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