SQL: Example Workaround for ORA-4091 Error (Doc ID 37861.1)

Last updated on SEPTEMBER 13, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 7.3.4.5 to 11.2.0.1 [Release 7.3.4 to 11.2]
PL/SQL - Version 7.3.4.5 to 11.2.0.1
Information in this document applies to any platform.
***Checked for relevance on 13th Sep 2016***

Goal

Oracle does not allow for the selection of data from a mutating table in a row trigger as it may lead to inconsistent data.

Attempt to do so, results in the following error:

ORA-04091 Table %s.%s is mutating, trigger/function may not see it

However, this operation can be performed within a statement trigger.

One way to work-around the mutating error within a row level trigger is to use a package PL/SQL table to store ROWIDs of updated records in a row trigger, and reprocess the updated records in a statement trigger.

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms