Exploring Mutating Table Errors and FORALL (9619)

The 5 January quiz tested players' knowledge of the fact that the mutating table error (ORA-04091) is raised differently for different ways of performing inserts and with a BEFORE row-level trigger.

Iudith Mentzel took the quiz as a starting point for some very interesting analysis, which I share here.

Hello Steven,

Following the quiz from January 5 about the mutating table error (ORA-04091), there was something in the explanation that arose my curiosity, so I tested it out and found something "half-strange".

Namely, it is the explanation of the correct choice [8740] that says the following:

"Oracle does not raise the mutating table error for the first row inserted. When it attempts to insert the row for the second element in the collection, the mutating table error is raised."

I performed the test below to prove that this is indeed the case and found the following:

CREATE TABLE plch_parts (
   partnum    NUMBER
 , partname   VARCHAR2 (30)
)
/

Table created.

CREATE OR REPLACE TRIGGER plch_parts_bir
   BEFORE INSERT
   ON plch_parts
   FOR EACH ROW
DECLARE
   cnt   NUMBER;
BEGIN
   -- just a control message
   DBMS_OUTPUT.put_line('BEFORE ROW trigger fired for '|| TO_CHAR(:new.partnum) );
   SELECT COUNT (*) INTO cnt FROM plch_parts;
END;
/

Trigger created.

/*
   Here we see that the mutating error happened indeed on the 2-nd row only,
   but it caused a rollback of the 1-st inserted row as well.
   This is usually NOT the case in a FORALL statement failure (for some other error),
   the results of the previous successful iterations are (generally) NOT rolled back
*/

DECLARE
   TYPE plch_parts_t IS TABLE OF plch_parts%ROWTYPE
                           INDEX BY PLS_INTEGER;
   t   plch_parts_t;
   cnt  NUMBER;
BEGIN
   t (1).partnum := 1;
   t (1).partname := 'A';
   t (2).partnum := 2;
   t (2).partname := 'B';

   FORALL i IN INDICES OF t
      INSERT INTO plch_parts
           VALUES t (i);
EXCEPTION
     WHEN OTHERS THEN
          DBMS_OUTPUT.put_line(SQLERRM);
        
          /* if the row inserted by the first iteration is not rolled back
             then here we should see "COUNT=1" */

          SELECT COUNT(*) INTO cnt FROM plch_parts;
          DBMS_OUTPUT.put_line('COUNT='||cnt);
END;
/

BEFORE ROW trigger fired for 1
BEFORE ROW trigger fired for 2

ORA-04091: table SCOTT.PLCH_PARTS is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.PLCH_PARTS_BIR", line 7
ORA-04088: error during execution of trigger 'SCOTT.PLCH_PARTS_BIR'

COUNT=0  =====>  this is strange  !!!

PL/SQL procedure successfully completed.

/*
   If we add a SAVE EXCEPTIONS , then the 1-st inserted row is NOT rolled back
   which is the expected behavior.

   However, the error displayed by SQLERRM is ORA-04091 and not the usual ORA-24381,
   which shows that in this case the entire FORALL is handled like a "single multirow INSERT",
   and not like an "array of (separate) INSERTS", as FORALL usually behaves.

   In spite of this, it does preserve the 1-st row inserted,
   so it only behaves "partially" as a FORALL ... SAVE EXCEPTIONS statement.
*/

DECLARE
   TYPE plch_parts_t IS TABLE OF plch_parts%ROWTYPE
                           INDEX BY PLS_INTEGER;

   t   plch_parts_t;
   cnt  NUMBER;
BEGIN
   t (1).partnum := 1;
   t (1).partname := 'A';
   t (2).partnum := 2;
   t (2).partname := 'B';

   FORALL i IN INDICES OF t SAVE EXCEPTIONS
      INSERT INTO plch_parts
           VALUES t (i);
EXCEPTION
     WHEN OTHERS THEN
          /* here we expect ORA-24381, and not ORA-04091,
             if the later is raised for the 2-nd row */
          DBMS_OUTPUT.put_line(SQLERRM);

          /* if the row inserted by the first iteration is not rolled back
             then here we should see "COUNT=1" */

          SELECT COUNT(*) INTO cnt FROM plch_parts;

          DBMS_OUTPUT.put_line('COUNT='||cnt);
          DBMS_OUTPUT.put_line('ERRORS='||SQL%BULK_EXCEPTIONS.COUNT);

          FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
          LOOP
             DBMS_OUTPUT.put_line(

'ERROR('||i||')='||SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||
                 '( '||SQL%BULK_EXCEPTIONS(i).ERROR_CODE||' )' );
          END LOOP;

END;
/

BEFORE ROW trigger fired for 1
BEFORE ROW trigger fired for 2

ORA-04091: table SCOTT.PLCH_PARTS is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.PLCH_PARTS_BIR", line 7
ORA-04088: error during execution of trigger 'SCOTT.PLCH_PARTS_BIR'

COUNT=1  =====> this is expected, but strange for a non-ORA-24381 error !

ERRORS=1
ERROR(1)=2( 4091 )

PL/SQL procedure successfully completed.

I checked the above in both 11.1.0.7.0 and 11.2.0.1.0 and the behavior is the same. I wonder whether there are other cases for which we can see something similar.