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... (AT) gmail (DOT) com> wrote:
No patch level information? Shame, shame, as that doesn't say much as
to which actual release you're using.

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

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

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

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.

Insert data, no commit issued prior to the select

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.

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

Comments embedded.
On May 13, 2:38*pm, kes <abi... (AT) gmail (DOT) com> wrote:
No patch level information? Shame, shame, as that doesn't say much as
to which actual release you're using.

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

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

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

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.

Insert data, no commit issued prior to the select

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.

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

Comments embedded.
On May 13, 2:38*pm, kes <abi... (AT) gmail (DOT) com> wrote:
No patch level information? Shame, shame, as that doesn't say much as
to which actual release you're using.

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

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

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

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.

Insert data, no commit issued prior to the select

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.

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

Comments embedded.
On May 13, 2:38*pm, kes <abi... (AT) gmail (DOT) com> wrote:
No patch level information? Shame, shame, as that doesn't say much as
to which actual release you're using.

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

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

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

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.

Insert data, no commit issued prior to the select

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.

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

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

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

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

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