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.
