Two (or more) DMLs inside one bulk collect operation loop

Question

I have problem with BULK COLLECT logic on Oracle 11g.

The original logic in stored procedure is:

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;

But I want to use BULK COLLECT feature.

I wrote something like that:

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;

Is this good approach for this problem?

What if I have more DML to perform?

Ok. My problem is more complex but I wanted to simplify it and enrich it with nice sample codes. Error OTHERS handing is not part of this problem. Maybe this will be more clear:

How this:

  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;

change to BULK COLLECT and FORALL statements ?

You should not catch a `WHEN OTHERS` without re-raising the error inside the procedure. The calling application may catch the error and display a nice error message but your running procedure should not ignore the error. *Log and Ignore* is a wrong philosophy. — Vincent Malgrat, Jan 29 '13 at 15:19
If `commit` and `rollback` are located outside this procedure, then why you simply have `return` without re-rising exceptions? How the caller would be informed about what is happened? — Egor Skriptunoff, Jan 29 '13 at 15:20
Why are you optimising a CURSOR FOR? Under 10g up they get internally optimised to perform at similar speeds to BULK COLLECT anyways. See: http://www.oracle.com/technetwork/issue-archive/2008/08-mar/o28plsql-095155.html — Kieran, Jan 29 '13 at 15:54

score 2 · Answer 1 · answered Jan 29 '13 at 15:34

Whether something is a "good approach" is very subjective-- it depends on what you are trying to compare against.

If we assume that your query against some_table has no predicate, it would almost certainly be more efficient (in addition to much less code) to work in sets rather than doing any sort of looping

PROCEDURE FOO(IN_FOO IN VARCHAR2) IS
BEGIN
  INSERT INTO other_table( c1, c2, c3 )
    SELECT col1, col2, col3
      FROM some_table;

  UPDATE third_table tt
     SET tt.c_sum = (SELECT st.col2 + st.col3
                       FROM some_table st
                      WHERE tt.c_id = st.col1)
   WHERE EXISTS( SELECT 1
                   FROM some_table st
                  WHERE tt.c_id = st.col1);
END;

Generally, a WHEN OTHERS exception handler is a bad idea. Catching an exception only to attempt to write it to DBMS_OUTPUT where the caller will have no idea that an error occurred, where the error stack is lost, and where there is no guarantee that the calling application has even allocated a buffer for the data to be written to is a bug waiting to happen. If you have this sort of code in your system, you will inevitably end up chasing hard to reproduce bugs because some piece of code somewhere encountered and swallowed an exception causing later bits of code to fail in unexpected ways.

I have a lot respect to Your knowledge but handling `OTHER` exceptions is out of the topic. I just want to know how to handle two (or more) DML operations using FORALL statement — WBAR, Jan 29 '13 at 15:56

score 2 · Answer 2 · edited May 23 '17 at 12:06

There is something wrong in your original procedure concerning error management, and this makes it hard to convert the logic to bulk processing.

Basically the logic of your first procedure is: run the two statements in a loop, exit successfully the first time you run into an error or at the end of the cursor, whichever happens first.

This is not a correct transaction logic. If your two statements work in tandem and the second fails, the first one is not rolled back !

What you probably want to do is: run the two statements in a loop; if you encounter an error, log the information and undo the changes, if not exit successfully. Undoing the changes is very easy to do in PL/SQL, you only need to let error propagate:

PROCEDURE FOO(IN_FOO IN VARCHAR2) IS
BEGIN
  FOR CUR IN (SELECT COL1,COL2,COL3 FROM SOME_TABLE) LOOP
    BEGIN
       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;
    EXCEPTION
       WHEN OTHERS THEN
          dbms_output.put_line(cur.col1/*...*/); -- log **useful** debug info
          RAISE;-- very important for transactional logic
    END;
  END LOOP;
END;

By the way DBMS_OUTPUT is not the best logging tool, you might want to create a logging table and an autonomous transaction procedure to insert the relevant error message and identifiers.

If you want to transform the above procedure by using bulk logic, your best move would be to use the method described by Justin Cave (single DML statements). When you use bulk arrays, you need to use the SAVE EXCEPTIONS clause if you want to log individual exceptions. Don't forget to re-raise the error. This should work:

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 SAVE EXCEPTIONS -- important clause
            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 SAVE EXCEPTIONS -- 
            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
            FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
               dbms_output.put_line('error '||i||':'||
                      SQL%BULK_EXCEPTIONS(i).error_code);
               dbms_output.put_line('col1='|| 
                      lt_cur(SQL%BULK_EXCEPTIONS(i).error_index).col1);-- 11g+
            END LOOP;
         raise_application_error(-20001, 'error in bulk processing');
      END;
   END LOOP;
END foo_fast;

Your solution has bug. In original statement when `INSERT` throws an exception (for example unique constraint) the update will NOT be processed and any further Insert&Update operations in loop. The processing in the loop will be stopped. In Your solution , Sir, when You collect exceptions by `SAVE EXCEPTIONS` NONE OF UPDATE will be processed and ALL INSERTS except wrong one. — WBAR, Jan 29 '13 at 16:12
@WBAR: it appears that the RAISE in the second case was not doing what I expected (rollback pending changes), I tested with a `RAISE_APPLICATION_ERROR` and now it behaves properly: any error will make the procedure undo its DML. The procedures are now atomic: they either fail completely without changing the state of the DB or complete successfully. — Vincent Malgrat, Jan 29 '13 at 17:00
But You got +1 for effort and nice TIP for other users with collecting BULK EXCEPTIONS — WBAR, Jan 30 '13 at 09:22

score 1 · Accepted Answer · answered Feb 19 '13 at 16:27

I found solution by using this kind of flow:

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 SAVE EXCEPTIONS
        INSERT INTO OTHER_TABLE (C1,C2,C3) VALUES (LT_CUR(I).COL1,LT_CUR(I).COL2,LT_CUR(I).COL3);
    EXCEPTION
      WHEN DML_EXCEPTION THEN
        FOR I IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
          DBMS_OUTPUT.PUT_LINE(SQLERRM(-SQL%BULK_EXCEPTIONS(1).ERROR_CODE));
          LT_CUR.DELETE(SQL%BULK_EXCEPTIONS(1).ERROR_INDEX);
    END;
    FORALL I IN INDICES OF LT_CUR 
        UPDATE THIRD_TABLE T SET T.C_SUM = LT_CUR(I).COL2 + LT_CUR(I).COL3 WHERE T.C_ID = LT_CUR(I).COL1);
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERROR || ': ' || SQLERRM);
END FOO_FAST;

In this flow:

All exceptions ocured in INSERT will be stored in SQL%BULK_EXCEPTIONS collection
Each exception will be logged by DBMS_OUTPUT.PUT_LINE (in real life in log table by AUTONOMOUS TRANSACTION procedure)
Each error index of LT_CUT will be removed by DELETE method on collection.
Only "good" lines will be used in UPDATE because INDICES OF clause allows a bulk operation on a sparse collection by removing the reference to specific elements

And if an error occurs in an UPDATE, the procedure will exit successfully with half the work done only, great :) — Vincent Malgrat, Feb 19 '13 at 17:15
My `UPDATE` is used to set up line have been processed so in my case works perfectly. :) — WBAR, Feb 19 '13 at 17:20

Two (or more) DMLs inside one bulk collect operation loop

3 Answers3