The autonomous transaction that fixed ORA-04091 and broke the answer

A trigger needed to read a table that the statement firing it was still changing. Oracle said no:

ORA-04091: table APP.INVOICES is mutating, trigger/function may not see it

Search that error and you will be told, over and over, to add PRAGMA AUTONOMOUS_TRANSACTION to the function. I did. The error went away immediately, and the feature kept working — for a while. Then the numbers started being wrong in a way nobody could reproduce on demand.

The pragma had not fixed anything. It had traded a loud failure for a silent one.

The setup#

Two tables, an invoice and the payments against it:

create table invoices (
  invoice_id  number generated always as identity primary key,
  total_due   number(12,2) not null,
  status      varchar2(20) default 'OPEN' not null
);

create table invoice_payments (
  payment_id  number generated always as identity primary key,
  invoice_id  number not null references invoices(invoice_id),
  amount      number(12,2) not null,
  paid_on     date default sysdate not null
);

And the function everything hangs on — how much is still owed:

create or replace function remaining_balance (p_invoice_id in number)
   return number
is
   v_due   number(12,2);
   v_paid  number(12,2);
begin
   select total_due into v_due
     from invoices
    where invoice_id = p_invoice_id;

   select nvl(sum(amount), 0) into v_paid
     from invoice_payments
    where invoice_id = p_invoice_id;

   return v_due - v_paid;
end;

The rule: when the balance reaches zero, the invoice is marked PAID.

Where the error actually came from#

The first ORA-04091 is the easy one. A row-level trigger on INVOICE_PAYMENTS calls REMAINING_BALANCE, which reads INVOICE_PAYMENTS — the table the statement is in the middle of changing. Everyone meets this one, and a compound trigger solves it: collect the keys in AFTER EACH ROW, do the reading in AFTER STATEMENT, where the restriction no longer applies.

So I wrote the compound trigger. And got ORA-04091 again — naming a different table.

The real chain was not one statement. It was a cascade:

1. The application updates INVOICES (a credit note changes TOTAL_DUE).
2. A trigger on INVOICES writes a row into INVOICE_PAYMENTS.
3. The compound trigger on INVOICE_PAYMENTS fires, and its AFTER STATEMENT section runs.
4. That section calls REMAINING_BALANCE, which reads INVOICES.
5. ORA-04091 — on INVOICES, still mutating from step 1.

A compound trigger lifts the restriction for its own table only. AFTER STATEMENT on INVOICE_PAYMENTS means the payments table has settled. It says nothing about INVOICES, which is mutating all the way down the cascade from step 1 and stays that way until the top-level statement finishes.

That is the part worth remembering: the table named in the error is the one the statement is changing, which is often not the table your code lives in.

The pragma: right about the error, wrong about everything else#

create or replace function remaining_balance (p_invoice_id in number)
   return number
is
   pragma autonomous_transaction;   -- makes ORA-04091 go away
   ...

It works, in the sense that the error stops. An autonomous transaction is a genuinely separate transaction with its own view of the database, so the mutating-table rule does not reach it.

But that separate view is the whole problem. An autonomous transaction cannot see the caller’s uncommitted changes. In step 1 the application updated TOTAL_DUE and has not committed — it cannot have, it is still inside the statement that fired the trigger. So the function reads INVOICES and gets the value from before the update.

An invoice whose total had just been reduced kept the old, larger TOTAL_DUE, so the balance never reached zero and the invoice was never marked PAID. Nothing failed. Nothing was logged. The only symptom was invoices sitting in OPEN that should not have been, in a pattern nobody could reproduce — because it only happened when the update and the payment arrived in the same transaction.

The pragma did not make the code correct. It made the code quiet.

When the pragma IS the right tool#

It has a real use. The question to ask is not “does this stop the error” but “do I want this work to be independent of the caller’s commit or rollback”:

A read-only function is the one case where the pragma can never help and can always hurt. It has nothing to commit independently, so the only thing the pragma changes is what the function is allowed to see — and that change is always a loss.

The fix: stop fighting for the data, defer the decision#

The trigger does not actually need the answer now. It needs the answer eventually. So let the trigger record that a decision is owed, and let something outside the cascade make it.

A queue table:

create table balance_recheck_queue (
  queue_id    number generated always as identity primary key,
  invoice_id  number not null,
  queued_on   date default sysdate not null,
  done_on     date
);

The trigger now writes a key and nothing else. It reads no table that could be mutating:

create or replace trigger payments_queue_recheck
for insert or update on invoice_payments
compound trigger

   type t_keys is table of number index by varchar2(40);
   g_keys t_keys;

after each row is
begin
   g_keys(to_char(:new.invoice_id)) := :new.invoice_id;
end after each row;

after statement is
   v_idx varchar2(40);
begin
   v_idx := g_keys.first;
   while v_idx is not null loop
      insert into balance_recheck_queue (invoice_id) values (g_keys(v_idx));
      v_idx := g_keys.next(v_idx);
   end loop;
   g_keys.delete;
end after statement;

end payments_queue_recheck;

Indexing the collection by the key gives deduplication for free: a hundred payment rows for one invoice collapse to one queue entry.

Then a procedure, called from outside any trigger, does the work:

create or replace procedure process_balance_rechecks
is
begin
   for q in (select queue_id, invoice_id
               from balance_recheck_queue
              where done_on is null
              order by queue_id) loop

      if remaining_balance(q.invoice_id) <= 0 then
         update invoices
            set status = 'PAID'
          where invoice_id = q.invoice_id;
      end if;

      update balance_recheck_queue
         set done_on = sysdate
       where queue_id = q.queue_id;

      commit;
   end loop;
end;

Run it on a schedule — see Oracle Scheduler jobs with DBMS_SCHEDULER for the job itself.

Three things changed, and each one removes a reason the original code was broken:

• The trigger no longer reads a table that might be mutating, so ORA-04091 cannot occur.
• REMAINING_BALANCE now runs from a top-level call with no trigger above it, so there is no mutating context to dodge and no pragma is needed.
• Because it is not autonomous, it sees committed data — and by the time the job runs, that includes everything the original transaction did.

The cost is honest and small: the status flips seconds later instead of inside the transaction. If the business genuinely needs it inside the transaction, the answer is not a cleverer trigger — it is to do the work in the procedure the application calls, where you own the whole unit and can read anything you like.

Rules I now follow#

• ORA-04091 names the table the statement is changing, not the table your code is in. When it points somewhere unexpected, read the cascade from the top.
• A compound trigger lifts the restriction for its own table only. It is the right fix for the direct case and no help at all two levels up a cascade.
• Never put PRAGMA AUTONOMOUS_TRANSACTION on a read-only function. It cannot fail loudly. It just answers with data from before the caller started.
• Reserve the pragma for work that must survive a rollback — audit rows, error logs, delivery records. That is the question it answers.
• When a trigger needs data it is not allowed to see yet, defer the decision rather than fighting for the data. A queue row plus a scheduled processor is less code than the workaround it replaces.
• “The error went away” is not “the code is correct.” A fix that removes a message without changing what the code reads deserves more suspicion, not less.

Versions#

Every behaviour here is database behaviour, not APEX behaviour, and none of it is version-specific: the mutating-table rule, compound triggers and autonomous-transaction visibility work the same way across currently supported Oracle releases. I hit this on 19c. The example above is a neutral rewrite of a case I debugged at work, not a transcript of it — the shape of the bug is what transfers, and it transfers completely.