Re: ORA-04091 Question

From: <fitzjarrell_at_cox.net>
Date: Tue, 13 May 2008 13:00:08 -0700 (PDT)
Message-ID: <8514dcdf-7bfc-4633-bf13-f600c691b11d@t54g2000hsg.googlegroups.com>


Comments embedded.
On May 13, 2:38 pm, kes <abi..._at_gmail.com> wrote:
> I'm using Oracle 10.r2

No patch level information? Shame, shame, as that doesn't say much as to which actual release you're using.

> There are no triggers on this table when I did a select * from
> all_triggers;
>

There doesn't need to be. The error also covers functions.

> Error report:
> ORA-04091: table "x" is mutating, trigger/function may not see it
> ORA-06512: at "package.stored procedure", line 250
> ORA-06512: at line 12
> 04091. 00000 -  "table %s.%s is mutating, trigger/function may not see
> it"
> *Cause:    A trigger (or a user defined plsql function that is
> referenced in
>            this statement) attempted to look at (or modify) a table
> that was
>            in the middle of being modified by the statement which
> fired it.
> *Action:   Rewrite the trigger (or function) so it does not read that
> table.
>
> Any ideas?

Certainly. You have a function selecting from a table involved in an uncommitted transaction in the same session which began that transaction.

>
> This is what I'm trying to do with the "package.stored_procedure" from
> the above error:
>
> --pseudo code (with pseudo comments)

You've modified records in Table Y, yet haven't committed the changes to complete this transaction.

> Insert into table Y (column a)
> Select column a
> From table X
> Where column b is null
>

Now you want to use a function to select from that table, where uncomitted data changes exist, and Oracle cannot obtain a readconsistent  view of that data since the same session which changed the data (and hasn't yet committed the changes) now wants to select from that same, modified table data from within a trigger/function.

> Loop through table Y
> Update table X
> Set column b = function (Y.a);
>
> (code to demonstrate what I'm doing)
>
> CREATE TABLE TABLE_X
> (
>   A VARCHAR2(40),
>   B VARCHAR2(40)
> );
>
> CREATE GLOBAL TEMPORARY TABLE TABLE_Y
> (
>   A VARCHAR2(40)
> )
> ON COMMIT PRESERVE ROWS
> ;
>
> create or replace function function_f
> ( p_a in varchar2
> ) return varchar2 as
> l_var varchar2(30);
> begin
>   select max(a) into l_var
>   from table_y;
>
>   return l_var || ' not really this simple'; -- This a join to an
> external database that links a and b
> end function_f;
>
> create or replace
> procedure procedure_p_helper as
> l_a varchar2(10);
>     cursor l_b_less_a is
>     select a
>     from table_y;
>   begin
>
>    open l_b_less_a;
>   loop
>     fetch l_b_less_a into l_a;
>     exit when l_b_less_a%notfound;
>       update table_x
>       set b = function_f(l_a);
>   end loop;
> end procedure_p_helper;
>

Insert data, no commit issued prior to the select

> create or replace
> procedure prodecure_p as
> begin
>   insert into table_y (a)
>   select a
>   from table_x
>   where b is null;

Select against table within a function after data is modified but before changes are committed. As stated earlier Oracle cannot guarantee the consistency of this image, hence the ORA-04091 error.

>   procedure_p_helper();
> end prodecure_p;
>
> insert into table_x(a, b)
> values ('1', null);
> insert into table_x(a, b)
> values ('2', '3');
> insert into table_x(a, b)
> values ('3', '4');
> insert into table_x(a, b)
> values ('4',  null);
>
> ---
> Alex Birchwww.lifesabirch.org

You'll need to commit (or roll back) the data changes before you try selecting from the changed table in a function or a trigger.

David Fitzjarrell Received on Tue May 13 2008 - 15:00:08 CDT

Original text of this message