title: Two (or more) DMLs inside one bulk collect operation loop
body: <p>I have problem with BULK COLLECT logic on Oracle 11g.</p>
  
  <p>The original logic in stored procedure is:</p>
  
  <pre><code>PROCEDURE FOO(IN_FOO IN VARCHAR2) IS
  BEGIN
    FOR CUR IN (SELECT COL1,COL2,COL3 FROM SOME_TABLE) LOOP
      INSERT INTO OTHER_TABLE (C1,C2,C3) VALUES (CUR.COL1,CUR.COL2,CUR.COL3);
      UPDATE THIRD_TABLE T SET T.C_SUM = CUR.COL2 + CUR.COL3 WHERE T.C_ID = CUR.COL1);
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLERROR || ': ' || SQLERRM);
  END FOO;
  </code></pre>
  
  <p>But I want to use <code>BULK COLLECT</code> feature.</p>
  
  <p>I wrote something like that:</p>
  
  <pre><code>PROCEDURE FOO_FAST(IN_FOO IN VARCHAR2) IS
    CURSOR CUR IS SELECT COL1,COL2,COL3 FROM SOME_TABLE;
    TYPE RT_CUR IS TABLE OF CUR%ROWTYPE;
    LT_CUR RT_CUR;
    DML_EXCEPTION EXCEPTION;
    PRAGMA EXCEPTION_INIT(DML_EXCEPTION, -24381);
  BEGIN
    OPEN CUR;
    LOOP
      FETCH CUR BULK COLLECT INTO LT_CUR LIMIT 1000;
      EXIT WHEN LT_CUR.COUNT = 0;
      BEGIN
        FORALL I IN 1 .. LT_CUR.COUNT 
          INSERT INTO OTHER_TABLE (C1,C2,C3) VALUES (LT_CUR(I).COL1,LT_CUR(I).COL2,LT_CUR(I).COL3);
        FORALL I IN 1 .. LT_CUR.COUNT 
          UPDATE THIRD_TABLE T SET T.C_SUM = LT_CUR(I).COL2 + LT_CUR(I).COL3 WHERE T.C_ID = LT_CUR(I).COL1);
      EXCEPTION
        WHEN DML_EXCEPTION THEN
          FORALL I IN 1 .. SQL%BULK_EXCEPTIONS(1).ERROR_INDEX-1
            UPDATE THIRD_TABLE T SET T.C_SUM = LT_CUR(I).COL2 + LT_CUR(I).COL3 WHERE T.C_ID = LT_CUR(I).COL1);
          DBMS_OUTPUT.PUT_LINE(SQLERRM(-SQL%BULK_EXCEPTIONS(1).ERROR_CODE));
          RETURN;
      END;
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLERROR || ': ' || SQLERRM);
  END FOO_FAST;
  </code></pre>
  
  <p>Is this good approach for this problem? </p>
  
  <p>What if I have more DML to perform?</p>
  
  <hr>
  
  <p>Ok. My problem is more complex but I wanted to simplify it and enrich it with nice sample codes. Error <code>OTHERS</code> handing is not part of this problem. Maybe this will be more clear:</p>
  
  <p>How this:</p>
  
  <pre><code>  FOR CUR IN (SELECT COL1,COL2,COL3 FROM SOME_TABLE) LOOP
      INSERT INTO OTHER_TABLE (C1,C2,C3) VALUES (CUR.COL1,CUR.COL2,CUR.COL3);
      UPDATE THIRD_TABLE T SET T.C_SUM = CUR.COL2 + CUR.COL3 WHERE T.C_ID = CUR.COL1);
    END LOOP;
  </code></pre>
  
  <p>change to <code>BULK COLLECT</code> and <code>FORALL</code> statements ?</p>