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

Comments embedded.
On May 13, 2:38*pm, kes <abi...@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 read-
consistent 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

> 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.

Dave,

Thank you so much. All I needed to do but to change it so that I wrote
to a variable, then used the varible; no commit required.
Insert into table Y (column a)
Select column a
From table X
Where column b is null

Loop through table Y
l_var = function (Y.a);
Update table X
Set column b = l_var;

Thank you again for leading me down the correct path.

Alex