Examining The Remains

"Don't you know anything at all about numbers?"
"Well, I don't think they're very important," snapped Milo, too embarrassed to admit the truth.
"NOT IMPORTANT!" roared the Dodecahedron, turning red with fury.  "Could you have tea for two 
without the two, or three blind mice without the three? Would there be four corners of the earth 
if there weren't a four? And how would you sail the seven seas without a seven?"
"All I meant was..." began Milo, but the Dodecahedron, overcome with emotion and shouting furiously,
carried right on.
"If you had high hopes, how would you know how high they were? And did you know that narrow escapes 
come in all different widths? Would you travel the whole wide world without ever knowing how wide it
was? And how could you do anything at long last," he concluded, waving his arms over his head, "without
knowing how long the last was? Why, numbers are the most beautiful and valuable things in the world.
Just follow me and I'll show you." He turned on his heel and stalked off into the cave.
-- Norton Juster, The Phantom Tollbooth

It used to be that a database was, well, a database and it didn’t contain lots of interesting mathematical and analytic functions, just tables, data and basic string and number functions. That’s changed, mainly because of what work we now need to do and what results we need to report; enterprise level databases are both more complicated in terms of extended functionality and easier in terms of end-users generating with a single function values that were the result of complicated calculations done outside of the database. It’s a testament to Oracle that, for the most part, these functions provide correct results. Unfortunately it appears that Oracle has ‘dropped the ball’ with what may be one of the simpler supplied functions, REMAINDER().

To start this odyssey a PL/SQL post was provided to illustrate the difference between MOD() and REMAINDER() and, unfortunately, the results from REMAINDER looked rather odd. I’ve provided the example from that post, modified to provide some context in the output. The ‘problem’ is that some of the remainders are negative which flies in the face of the mathematical definition of a remainder, which is:

In mathematics, the remainder is the amount "left over" after performing some computation.  In arithmetic, the remainder
is the integer "left over" after dividing one integer by another to produce an integer quotient (integer division).

The thought in my mind and that of every mathematician on this planet is that a remainder will never be negative. It’s what is ‘left over’ and you can’t ‘owe someone’ a remainder; besides being impossible according to the definition it’s also just not polite. For example, 15 divided by 2 has a remainder of 1, not -1. Let’s run the modified example and see what it returns:

SQL> 
SQL> --
SQL> -- REMAINDER doesn't return the correct remainder for
SQL> -- divisors of odd numbers that are powers of 2
SQL> --
SQL> -- The following example returns correct remainders
SQL> -- for all listed divisors of 15 except the
SQL> -- powers of 2, where the formula generates 16 as the
SQL> -- (n*X) value, thus displaying a negative remainder:
SQL> --
SQL> BEGIN
  2  	DBMS_OUTPUT.put_line ('MOD(15,2):'||MOD (15, 2));
  3  	DBMS_OUTPUT.put_line ('REMAINDER(15,2):'||REMAINDER (15, 2));
  4  	DBMS_OUTPUT.put_line ('MOD(15,3):'||MOD (15, 3));
  5  	DBMS_OUTPUT.put_line ('REMAINDER(15,3):'||REMAINDER (15, 3));
  6  	DBMS_OUTPUT.put_line ('MOD(15,4):'||MOD (15, 4));
  7  	DBMS_OUTPUT.put_line ('REMAINDER(15,4):'||REMAINDER (15, 4));
  8  	DBMS_OUTPUT.put_line ('MOD(15,5):'||MOD (15, 5));
  9  	DBMS_OUTPUT.put_line ('REMAINDER(15,5):'||REMAINDER (15, 5));
 10  	DBMS_OUTPUT.put_line ('MOD(15,6):'||MOD (15, 6));
 11  	DBMS_OUTPUT.put_line ('REMAINDER(15,6):'||REMAINDER (15, 6));
 12  	DBMS_OUTPUT.put_line ('MOD(15,7):'||MOD (15, 7));
 13  	DBMS_OUTPUT.put_line ('REMAINDER(15,7):'||REMAINDER (15, 7));
 14  	DBMS_OUTPUT.put_line ('MOD(15,8):'||MOD (15, 8));
 15  	DBMS_OUTPUT.put_line ('REMAINDER(15,8):'||REMAINDER (15, 8));
 16  END;
 17  /
MOD(15,2):1                                                                     
REMAINDER(15,2):-1                                                              
MOD(15,3):0                                                                     
REMAINDER(15,3):0                                                               
MOD(15,4):3                                                                     
REMAINDER(15,4):-1                                                              
MOD(15,5):0                                                                     
REMAINDER(15,5):0                                                               
MOD(15,6):3                                                                     
REMAINDER(15,6):3                                                               
MOD(15,7):1                                                                     
REMAINDER(15,7):1                                                               
MOD(15,8):7                                                                     
REMAINDER(15,8):-1                                                              

PL/SQL procedure successfully completed.

Why, oh why, does this happen? Oracle takes a ‘shortcut’ to get there, using a formula that, as far as I can tell, isn’t checking all that it needs to check before ‘spitting out’ the answer. That formula is shown below:

					R = m - (n*X)

where R is the remainder, m is the dividend, n is the divisor and X is an integer where n*X should be <= m

Notice what check is missing? For some odd reason Oracle never checks to see if the product (n*X) is less than or equal to m. Since Oracle calculates X by performing ROUND(m/n,0) fractional parts of a quotient, when they are .5 or greater, round up to the next highest integer. For the case of 15 divided by even numbers the calculated value of X results in (n*X) being equal to 16. Once that happens the remainders all end up as -1 which, by definition, is impossible.

Fiddle-dee-dee, what are we to do? One option is to use MOD() instead, since the results for MOD() don’t exhibit the same problem. Another option, if production code can’t be changed, is to create another remainder function, possibly in the application schema, with a private synonym calling it ‘REMAINDER’. One example of such a function is shown below:

SQL> 
SQL> --
SQL> -- Create a function to return the correct remainder - does not
SQL> -- generate values greater than the supplied target so the remainders
SQL> -- are not negative
SQL> --
SQL> create or replace function remainder_func(p_num1 in number, p_num2 in number)
  2  return number is
  3  	     v_rmdr  number;
  4  	     v_x     number:-1;
  5  begin
  6  	     v_x :- trunc(p_num1/p_num2, 0);
  7  	     v_rmdr :- p_num1 - (v_x * p_num2);
  8  	     return(v_rmdr);
  9  end;
 10  /

Function created.

SQL> 

So, let’s see what results are returned, since this function uses TRUNC() instead of ROUND():

SQL> BEGIN
  2  	DBMS_OUTPUT.put_line ('MOD(15,2):'||MOD (15, 2));
  3  	DBMS_OUTPUT.put_line ('REMAINDER_func(15,2):'||REMAINDER_func (15, 2));
  4  	DBMS_OUTPUT.put_line ('MOD(15,3):'||MOD (15, 3));
  5  	DBMS_OUTPUT.put_line ('REMAINDER_func(15,3):'||REMAINDER_func (15, 3));
  6  	DBMS_OUTPUT.put_line ('MOD(15,4):'||MOD (15, 4));
  7  	DBMS_OUTPUT.put_line ('REMAINDER_func(15,4):'||REMAINDER_func (15, 4));
  8  	DBMS_OUTPUT.put_line ('MOD(15,5):'||MOD (15, 5));
  9  	DBMS_OUTPUT.put_line ('REMAINDER_func(15,5):'||REMAINDER_func (15, 5));
 10  	DBMS_OUTPUT.put_line ('MOD(15,6):'||MOD (15, 6));
 11  	DBMS_OUTPUT.put_line ('REMAINDER_func(15,6):'||REMAINDER_func (15, 6));
 12  	DBMS_OUTPUT.put_line ('MOD(15,7):'||MOD (15, 7));
 13  	DBMS_OUTPUT.put_line ('REMAINDER_func(15,7):'||REMAINDER_func (15, 7));
 14  	DBMS_OUTPUT.put_line ('MOD(15,8):'||MOD (15, 8));
 15  	DBMS_OUTPUT.put_line ('REMAINDER_func(15,8):'||REMAINDER_func (15, 8));
 16  END;
 17  /
MOD(15,2):1                                                                     
REMAINDER_func(15,2):1                                                          
MOD(15,3):0                                                                     
REMAINDER_func(15,3):0                                                          
MOD(15,4):3                                                                     
REMAINDER_func(15,4):3                                                          
MOD(15,5):0                                                                     
REMAINDER_func(15,5):0                                                          
MOD(15,6):3                                                                     
REMAINDER_func(15,6):3                                                          
MOD(15,7):1                                                                     
REMAINDER_func(15,7):1                                                          
MOD(15,8):7                                                                     
REMAINDER_func(15,8):7                                                          

PL/SQL procedure successfully completed.

Wow, golly gee whilikers, the results are correct! Of course that should be expected since there is no possibility that (n*X) will be greater than m. Let’s take a side trip and see how ROUND() and TRUNC() provide different results for odd numbers divided by even numbers, using 15 as a ‘test subject’:

SQL> 
SQL> --
SQL> -- ROUND() doesn't work for this example as it
SQL> -- generates values that are powers of 2
SQL> --
SQL> 
SQL> create or replace procedure remainder_test(p_num1 in number, p_num2 in number)
  2  is
  3  	     v_t_rmdr	     number;
  4  	     v_r_rmdr	     number;
  5  	     v_tx    number:-1;
  6  	     v_rx    number:-1;
  7  begin
  8  	     dbms_output.put_line('---------------------------------------------------------------------');
  9  	     v_tx :- trunc(p_num1/p_num2, 0);
 10  	     v_rx :- round(p_num1/p_num2, 0);
 11  	     v_t_rmdr :- p_num1 - (v_tx * p_num2);
 12  	     v_r_rmdr :- p_num1 - (v_rx * p_num2);
 13  	     dbms_output.put_line('Rounded:   '||v_rx||' (n*X): '||v_rx*p_num2||' Remainder: '||v_r_rmdr);
 14  	     dbms_output.put_line('Truncated: '||v_tx||' (n*X): '||v_tx*p_num2||' Remainder: '||v_t_rmdr);
 15  	     dbms_output.put_line('---------------------------------------------------------------------');
 16  end;
 17  /

Procedure created.

SQL> 
SQL> BEGIN
  2  	REMAINDER_test (15, 2);
  3  	REMAINDER_test (15, 3);
  4  	REMAINDER_test (15, 4);
  5  	REMAINDER_test (15, 5);
  6  	REMAINDER_test (15, 6);
  7  	REMAINDER_test (15, 7);
  8  	REMAINDER_test (15, 8);
  9  END;
 10  /
---------------------------------------------------------------------           
Rounded:   8 (n*X): 16 Remainder: -1                                            
Truncated: 7 (n*X): 14 Remainder: 1                                             
---------------------------------------------------------------------           
---------------------------------------------------------------------           
Rounded:   5 (n*X): 15 Remainder: 0                                             
Truncated: 5 (n*X): 15 Remainder: 0                                             
---------------------------------------------------------------------           
---------------------------------------------------------------------           
Rounded:   4 (n*X): 16 Remainder: -1                                            
Truncated: 3 (n*X): 12 Remainder: 3                                             
---------------------------------------------------------------------           
---------------------------------------------------------------------           
Rounded:   3 (n*X): 15 Remainder: 0                                             
Truncated: 3 (n*X): 15 Remainder: 0                                             
---------------------------------------------------------------------           
---------------------------------------------------------------------           
Rounded:   3 (n*X): 18 Remainder: -3                                            
Truncated: 2 (n*X): 12 Remainder: 3                                             
---------------------------------------------------------------------           
---------------------------------------------------------------------           
Rounded:   2 (n*X): 14 Remainder: 1                                             
Truncated: 2 (n*X): 14 Remainder: 1                                             
---------------------------------------------------------------------           
---------------------------------------------------------------------           
Rounded:   2 (n*X): 16 Remainder: -1                                            
Truncated: 1 (n*X): 8 Remainder: 7                                              
---------------------------------------------------------------------           

PL/SQL procedure successfully completed.

SQL> 

There’s quite a difference between ROUND() and TRUNC(); to be fair MOD() uses FLOOR() in the calculations to generate MOD values and, as mentioned before, REMAINDER() uses ROUND(), probably in an effort to make the two functions internally different. Using a bit more code the ROUND() option could also work:

SQL> --
SQL> -- Create a function to return the correct remainder - does not
SQL> -- generate values greater than the supplied target so the remainders
SQL> -- are not negative
SQL> --
SQL> -- Uses ROUND() to compute values
SQL> --
SQL> create or replace function remainder_func(p_num1 in number, p_num2 in number)
  2  return number is
  3  	     v_rmdr  number;
  4  	     v_x     number:=1;
  5  begin
  6  	     v_x := round(p_num1/p_num2, 0);
  7  	     if v_x*p_num2 > p_num1 then
  8  		     v_x := v_x -1;
  9  	     end if;
 10  	     v_rmdr := p_num1 - (v_x * p_num2);
 11  	     return(v_rmdr);
 12  end;
 13  /

Function created.

SQL> 
SQL> BEGIN
  2  	DBMS_OUTPUT.put_line ('MOD(15,2):'||MOD (15, 2));
  3  	DBMS_OUTPUT.put_line ('REMAINDER_func(15,2):'||REMAINDER_func (15, 2));
  4  	DBMS_OUTPUT.put_line ('MOD(15,3):'||MOD (15, 3));
  5  	DBMS_OUTPUT.put_line ('REMAINDER_func(15,3):'||REMAINDER_func (15, 3));
  6  	DBMS_OUTPUT.put_line ('MOD(15,4):'||MOD (15, 4));
  7  	DBMS_OUTPUT.put_line ('REMAINDER_func(15,4):'||REMAINDER_func (15, 4));
  8  	DBMS_OUTPUT.put_line ('MOD(15,5):'||MOD (15, 5));
  9  	DBMS_OUTPUT.put_line ('REMAINDER_func(15,5):'||REMAINDER_func (15, 5));
 10  	DBMS_OUTPUT.put_line ('MOD(15,6):'||MOD (15, 6));
 11  	DBMS_OUTPUT.put_line ('REMAINDER_func(15,6):'||REMAINDER_func (15, 6));
 12  	DBMS_OUTPUT.put_line ('MOD(15,7):'||MOD (15, 7));
 13  	DBMS_OUTPUT.put_line ('REMAINDER_func(15,7):'||REMAINDER_func (15, 7));
 14  	DBMS_OUTPUT.put_line ('MOD(15,8):'||MOD (15, 8));
 15  	DBMS_OUTPUT.put_line ('REMAINDER_func(15,8):'||REMAINDER_func (15, 8));
 16  END;
 17  /
MOD(15,2):1                                                                     
REMAINDER_func(15,2):1                                                          
MOD(15,3):0                                                                     
REMAINDER_func(15,3):0                                                          
MOD(15,4):3                                                                     
REMAINDER_func(15,4):3                                                          
MOD(15,5):0                                                                     
REMAINDER_func(15,5):0                                                          
MOD(15,6):3                                                                     
REMAINDER_func(15,6):3                                                          
MOD(15,7):1                                                                     
REMAINDER_func(15,7):1                                                          
MOD(15,8):7                                                                     
REMAINDER_func(15,8):7                                                          

PL/SQL procedure successfully completed.

SQL> 

but I think the first function is slightly more efficient. Additionally the second function essentially ‘re-invents’ FLOOR(), so why not simply use FLOOR() to get the job done:

SQL>
SQL> --
SQL> -- Create a function to return the correct remainder - does not
SQL> -- generate values greater than the supplied target so the remainders
SQL> -- are not negative
SQL> --
SQL> -- Uses FLOOR() to compute values
SQL> --
SQL> create or replace function remainder_func(p_num1 in number, p_num2 in number)
  2  return number is
  3          v_rmdr  number;
  4          v_x     number:=1;
  5  begin
  6          v_x := floor(p_num1/p_num2);
  7          v_rmdr := p_num1 - (v_x * p_num2);
  8          return(v_rmdr);
  9  end;
 10  /

Function created.

SQL>
SQL> BEGIN
  2     DBMS_OUTPUT.put_line ('MOD(15,2):'||MOD (15, 2));
  3     DBMS_OUTPUT.put_line ('REMAINDER_func(15,2):'||REMAINDER_func (15, 2));
  4     DBMS_OUTPUT.put_line ('MOD(15,3):'||MOD (15, 3));
  5     DBMS_OUTPUT.put_line ('REMAINDER_func(15,3):'||REMAINDER_func (15, 3));
  6     DBMS_OUTPUT.put_line ('MOD(15,4):'||MOD (15, 4));
  7     DBMS_OUTPUT.put_line ('REMAINDER_func(15,4):'||REMAINDER_func (15, 4));
  8     DBMS_OUTPUT.put_line ('MOD(15,5):'||MOD (15, 5));
  9     DBMS_OUTPUT.put_line ('REMAINDER_func(15,5):'||REMAINDER_func (15, 5));
 10     DBMS_OUTPUT.put_line ('MOD(15,6):'||MOD (15, 6));
 11     DBMS_OUTPUT.put_line ('REMAINDER_func(15,6):'||REMAINDER_func (15, 6));
 12     DBMS_OUTPUT.put_line ('MOD(15,7):'||MOD (15, 7));
 13     DBMS_OUTPUT.put_line ('REMAINDER_func(15,7):'||REMAINDER_func (15, 7));
 14     DBMS_OUTPUT.put_line ('MOD(15,8):'||MOD (15, 8));
 15     DBMS_OUTPUT.put_line ('REMAINDER_func(15,8):'||REMAINDER_func (15, 8));
 16  END;
 17  /
MOD(15,2):1
REMAINDER_func(15,2):1
MOD(15,3):0
REMAINDER_func(15,3):0
MOD(15,4):3
REMAINDER_func(15,4):3
MOD(15,5):0
REMAINDER_func(15,5):0
MOD(15,6):3
REMAINDER_func(15,6):3
MOD(15,7):1
REMAINDER_func(15,7):1
MOD(15,8):7
REMAINDER_func(15,8):7

PL/SQL procedure successfully completed.

SQL>

FLOOR() returns the highest value that is not larger than the supplied argument (in this case 15 divided by some number), rounding down when necessary. It’s interesting that Oracle used a different formula to compute REMAINDER() values that could (and does) produce negative results.

A discussion ensued on Twitter that ROUND() contained a bug, yet nothing was further from the truth. ROUND() behaves exactly as it should; the issue is that ROUND() can generate a number that, when multiplied by the supplied divisor, is larger than the supplied dividend, producing remainders that are negative. So the ‘bug’, if you will, is in how Oracle implemented REMAINDER() [R=m-(n*X)], and that ‘bug’ can be fixed by coding the function to produce (n*X) values that are always less than or equal to m.

No matter how thoroughly testing is done it seems that one of two problems will rear its ugly head. One is that as hard as you try you can’t generate every bit of data the application will see ‘in the wild’. The other is you can overlook simple examples in favor of the more complex. This is, of course, why we file bug reports with software vendors, because we caught something they didn’t. It is, I suppose, the ‘circle of life’ for software.

Now the remainder is up to you.

“It Doesn’t Work”

“Since you got here by not thinking, it seems reasonable to expect that, in order to get out, you must start thinking.”
— Norton Juster, The Phantom Tollbooth

The Devil is in the details, however, in support forums and newsgroups, the Devil is just as present when the details are either sketchy or non-existent. Volunteers in such forums and newsgroups quite often hear the dreaded words “it doesn’t work”, followed by … nothing. No detail, no explanation, no follow-up, nothing. If you walked in to your doctor and said, with no context, “it hurts” your doctor would be at a loss with respect to any reasonable attempt at a diagnosis. WHAT hurts? Where? When did it start? All questions your doctor would immediately ask in hopes of finding an answer. Support volunteers have no crystal balls, no Ouija boards, no Tarot cards and are not mind readers so leaving out important information makes it impossible for them to assess your situation.

Encountering a problem for the first time can be frustrating, that’s understood, so it stands to reason that you may not have much of a clue on how to ask your question. How should you frame questions in such forums? I blogged about that here so I won’t repeat myself. Suffice it to say that the more detail and explanation you can provide to support volunteers the better the responses will be. Also try your best to find an answer before you go to the forums and newsgroups. The more YOU can do to find an answer outside of the forums the more likely you’ll find help in the forums when you really need it.

Members of such forums see all sorts of questions, many of them repeatedly, so supplying as much information as you can when you first ask your question is important. If you ‘miss the mark’, so to speak, when supplying relevant details these volnteers will ask you questions to narrow down the possibilities. Doing so doesn’t mean they don’t know what they are talking about, it means you didn’t make your situation clear enough to generate a usable answer. Another response you might encounter for ambiguous questions is “That depends…”, and that isn’t a red flag of ignorance, it’s the volunteers trying to get you to be more specific with your details. Remember, the more they know about your problem the better able they will be to find you an answer.

Patience is a virtue; forum members don’t sit at their computers every second of every day just waiting for you to ask questions so you shouldn’t expect immediate responses. Sometimes it may take a day to get an initial response, so that shouldn’t worry you. In some cases there may be forum members who tend to answer questions in certain specific areas, and your question may fall into one of those areas, and those members may be offline when you get the time to ask. Just because you don’t get an immediate response in no way indicates you and your question are being ignored, and a delay in responses doesn’t mean you need to post your question again and again. Rest assured you will have the attention of forum members and someone will provide a response, even if it’s to ask for more information. Quality takes time.

It bears repeating that the brilliant souls in these forums and newsgroups are volunteers, not paid support personnel, and they are in these forums because they actually, honestly want to help by sharing knowledge they have gained through years of experience. They are giving their time to help you and others like you and it would be wise to remember that. Having a sense of entitlement has no place in these forums; behaving as though you are owed an answer is probably the surest way to keep those volunteers from helping you. Treat them as you would want to be treated and you may find that these volunteers will go the extra mile to help you.

It’s definitely worth thinking about.

“It’s … MUTATING!!!!”

“The most important reason for going from one place to another is to see what's in between.” 
― Norton Juster, The Phantom Tollbooth   

The mutating table error (ORA-04091) thrown by Oracle can be a familiar sight throughout a DBA’s career, along with the usual question of ‘Why?’. The error itself is not difficult to understand nor is it hazardous to the table data. Let’s look at why the error is thrown, why it’s not gong to create data problems and how to possibly fix it.

The first thing to realize is the table isn’t actually mutating; the error is thrown because of the read consistency mechanism Oracle employs to ensure against ‘dirty reads’, which are reads of uncommitted changes. For all but the session performing the data modifications Oracle will use redo blocks to reconstruct the data image as of the starting time of the query. This prevents any uncommitted changes from being read. For the session performing the modifications the uncommitted changes are visible, but only after the modifications have completed. Insert 10 rows into a table, then query the count and you’ll see there are 10 more rows than when you started. Every other session sees only the committed results. The mutating issue surfaces when a regular trigger attempts to modify the same table that was modified by the driving insert, update or delete statement. Since Oracle allows the modifying session to see its own changes Oracle tries to execute the trigger but fails when another DML statement tries to change incompletely modified data, meaning the change IS made but the update statement hasn’t yet completed all actions it started, such as firing triggers. Since the insert/update/delete can’t complete until the trigger successfully executes, and executing the trigger would pile changes on top of changes in the middle of a transaction, Oracle doesn’t allow it and throws the error.

In a promoted video on YouTube the following statement is made:

“… that you are breaking this rule and stop your table data and schema objects from corruption.”

Trying to modify data that is in the middle of another modification operation won’t corrupt it since Oracle won’t allow such updates to occur. Depending on the type of trigger involved Oracle will either try to modify existing data before the requested modifications are processed or Oracle will process the insert/update then try to modify the resulting data before the transaction ends. In either case since an active modification is in progress Oracle won’t allow a second modification from the same session on the incompletely altered data. Were this safety mechanism not in place it would be possible to ‘mangle’ table data into an irreversible state and thus ‘corrupt’ it, however schema object changes are effected through data definition language (DDL) changes which are preceded by a commit and followed by one. DML, such as insert, update and delete statements, cannot corrupt schema object structure.

Getting around such an error can be done in two ways: using a compound trigger if you’re using Oracle 11g or later, or by using the autonomous_transaction pragma. The following example shows how to code such a trigger to avoid the ORA-04091 error:

create or replace procedure set_comm(p_empno in number, p_sal in number, p_oldsal number)
is
pragma autonomous_transaction;
begin
	if p_sal >= p_oldsal*1.10 then
		update emp
		set comm = p_sal*.10
		where empno = p_empno;
	end if;
end;
/

create or replace trigger after_emp_updt
after update of sal on emp
for each row
begin
		set_comm(:new.empno, :new.sal, :old.sal);
end;
/

The trigger is simple, and uses a procedure declared to execute in an autonomous transaction. Autonomous transactions occur in a new process spawned from the calling process, and are thus subject to the standard read consistency mechanism Oracle employs. The procedure can perform its work outside of the original session that fired the trigger, getting around the ORA-04091 error entirely.

The compound trigger operates a bit differently, doing all of its table access before the triggering event causes one of the sections to fire, thus there is no in-process data update to interrupt. Let’s look at an example from a previous article to see how this is done:

SQL> create or replace trigger check_raise_on_avg
  2  for update of sal on emp
  3  COMPOUND TRIGGER
  4    Twelve_Percent        constant number:=0.12;
  5
  6    -- Declare collection type and variable:
  7
  8    TYPE Department_Salaries_t  IS TABLE OF Emp.Sal%TYPE
  9                                  INDEX BY VARCHAR2(80);
 10    Department_Avg_Salaries     Department_Salaries_t;
 11    TYPE Sal_t             IS TABLE OF Emp.Sal%TYPE;
 12    Avg_Salaries                Sal_t;
 13    TYPE Deptno_t       IS TABLE OF Emp.Deptno%TYPE;
 14    Department_IDs              Deptno_t;
 15
 16    BEFORE STATEMENT IS
 17    BEGIN
 18      SELECT               AVG(e.Sal), NVL(e.Deptno, -1)
 19        BULK COLLECT INTO  Avg_Salaries, Department_IDs
 20        FROM               Emp e
 21        GROUP BY           e.Deptno;
 22      FOR j IN 1..Department_IDs.COUNT() LOOP
 23        Department_Avg_Salaries(Department_IDs(j)) := Avg_Salaries(j);
 24      END LOOP;
 25    END BEFORE STATEMENT;
 26
 27    AFTER EACH ROW IS
 28    BEGIN
 29      IF :NEW.Sal - :Old.Sal >
 30        Twelve_Percent*Department_Avg_Salaries(:NEW.Deptno)
 31      THEN
 32        Raise_Application_Error(-20000, 'Raise too large');
 33      END IF;
 34    END AFTER EACH ROW;
 35  END Check_Raise_On_Avg;
 36  /

Trigger created.

SQL> select empno, sal from emp;

     EMPNO        SAL
---------- ----------
      7369        800
      7499       1600
      7521       1250
      7566       2975
      7654       1250
      7698       2850
      7782       2450
      7788       3000
      7839       5000
      7844       1500
      7876       1100
      7900        950
      7902       3000
      7934       1300

14 rows selected.

SQL>
SQL> update emp set sal=sal*1.10 where empno = 7369;

1 row updated.

SQL>
SQL> select empno, sal from emp;

     EMPNO        SAL
---------- ----------
      7369        880
      7499       1600
      7521       1250
      7566       2975
      7654       1250
      7698       2850
      7782       2450
      7788       3000
      7839       5000
      7844       1500
      7876       1100
      7900        950
      7902       3000
      7934       1300

14 rows selected.

SQL>

Updates occur even though the trigger is also selecting data from the table being modified, all possible by the way Oracle executes compound triggers.

The mutating table error is inconvenient but it really isn’t a danger to your data or your schema objects. It’s a read consistency problem that can be overcome by using autonomous transactions or compound triggers. Knowing that should make the DBAs life a little bit easier.

Sometimes what’s in between can be interesting.