title: Is this how non-bulk binding PL/SQL code should be translated to bulk-binding code, and is there ever a reason to forgo buk binding?
body: <p>(This is all Oracle 10g):</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION bar(...)
  IS
      v_first_type VARCHAR2(100) ;
      v_second_type VARCHAR2(100);
  
      CURSOR cur IS SELECT a,b FROM source_table ;
      v_a int;
      v_b char;
  BEGIN
      OPEN cur;
      &lt;&lt;l_next&gt;&gt; --10G doesn't have the continue statement.
      LOOP
          FETCH cur INTO v_a, v_b ;
          EXIT WHEN cur%NOTFOUND ;
  
          --Ignore Record Case: ignore the record entirely
          IF a == -1 THEN
              -- do something
              GOTO l_next ; --10g doesn't have the continue statement.
          ELSE
              -- do something else
              v_first := 'SUCCESS' ;
          END IF;
  
          -- Transform Case:
          IF b == 'z' THEN
              -- do something
              v_second := 'something';
          ELSE
              -- do something
              v_second := 'something else';
          END IF;
  
  
          INSERT INTO report_table VALUES (v_first, v_second);
      END LOOP;
      CLOSE cur;
  EXCEPTION 
      ...
  END;
  </code></pre>
  
  <p>I'm at my first job out of college. I'm looking through some legacy code that looks like the general framework above (except it is several hundreds of lines long and uses much more complicated processing (no set-based solution possible)).</p>
  
  <p>Its pulling lots of rows from one table into a cursor, looping through the cursor, making transformations to it, and inserting the results into a report table. The cursor does not insert every record -- if there is something wrong with the record or if it doesn't like it for whatever reason, it will skip the record without inserting it (see the GOTO statements).</p>
  
  <p>Problem 1:
  The inserts are occurring one by one inside the loop, instead of doing a FORALL at the end, outside of the loop.</p>
  
  <p>Problem 2:
  The cursor does not use BULK COLLECT.</p>
  
  <p>On top of this, there is a stored procedure that has a cursor which again doesn't use BULK COLLECT, and it issues this function while looping through the records in the cursor. One commit is issued at the end of each record that's been looped over. There are no commits in the function I'm writing about here.</p>
  
  <p>I want to rewrite the code to look like this:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION bar(...)
  IS
      CURSOR cur IS SELECT a,b FROM source_table ;
  
      TYPE t_source IS TABLE OF cur%ROWTYPE INDEX BY PLS_INTEGER;
      TYPE t_report IS TABLE OF destination_table%ROWTYPE INDEX BY PLS_INTEGER;
      v_sources t_source;
      v_reports t_report
      v_report_inx INT := 0; -- To Prevent Sparse Collection
  BEGIN
      OPEN cur;
      &lt;&lt;l_next&gt;&gt; --10G doesn't have the continue statement.
      LOOP
          FETCH cur BULK COLLECT INTO v_sources LIMIT 100 ;
          EXIT WHEN v_sources.count = 0 ;
  
          FOR i IN 1 .. v_sources LOOP
              --Ignore Record Case: ignore the record entirely
              IF v_sources(i).a == -1 THEN
                  -- do something
                  GOTO l_next ; --10g doesn't have the continue statement.
              ELSE
                  -- do something else
                  v_reports(v_report_inx).first := 'SUCCESS' ;
              END IF;
  
              -- Transform Case:
              IF v_sources(i).b == 'z' THEN
                  -- do something
                  v_reports(v_report_inx).second := 'something';
              ELSE
                  -- do something
                  v_reports(v_report_inx).second := 'something else';
              END IF;
  
              v_report_inx := v_report_inx + 1;
          END LOOP;
  
  
      END LOOP;
  
      FORALL i in 1 .. v_reports.count
              INSERT INTO report_table (first, second) VALUES (v_reports(i).first, v_reports(i).v_second);
  
      CLOSE cur;
  EXCEPTION 
      ...
  END;
  </code></pre>
  
  <p>The material changes are 1) using BULK COLLECT into an associative array, and 2) using FORALL out of a different associative array.</p>
  
  <p>I have two questions:</p>
  
  <p>1) Based on the framework I provided in the first snippet, are my alterations the most superior way to do it? Would you do it another way?</p>
  
  <p>2) Is there any reason that I am not thinking of which would make someone not use BULK COLLECT and FORALL? Perhaps some complicated processing that I haven't realized yet in the legacy code? The code was originally formed in 2002 (so I would assume 8i or 9i) but has been updated since then. <a href="http://docs.oracle.com/cd/B10501_01/appdev.920/a96624/05_colls.htm#23723" rel="nofollow">9i had bulk binding</a>. <a href="http://docs.oracle.com/cd/F49540_01/DOC/server.815/a68020/ch2.htm#104106" rel="nofollow">8i also</a> had bulk binding. Both had associative arrays. So I feel like there must be a reason why they hadn't been using bulk binding.</p>