ORA-04091 Question

From: kes <abirch_at_gmail.com>
Date: Tue, 13 May 2008 12:38:28 -0700 (PDT)
Message-ID: <0c717416-66d9-46a5-92b3-f04c08c3be75@r66g2000hsg.googlegroups.com>

I'm using Oracle 10.r2
There are no triggers on this table when I did a select * from all_triggers;

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?

This is what I'm trying to do with the "package.stored_procedure" from the above error:

--pseudo code (with pseudo comments)
Insert into table Y (column a)
Select column a
From table X
Where column b is null

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;

create or replace
procedure prodecure_p as
begin
  insert into table_y (a)
  select a
  from table_x
  where b is null;
  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 Birch
www.lifesabirch.org

Received on Tue May 13 2008 - 14:38:28 CDT