ORA-04091: table is mutating, trigger/function
MDK999Mar 7 2013 — edited Mar 8 2013I am using oracle 11g R2 - 11.2.0.1
I have following function to fetch the ID from name supplied
create or replace
FUNCTION get_group_id
(p_groupname IN group_list.groupname%TYPE)
RETURN group_list.group_id%TYPE
AS
v_group_id group_list.group_id%TYPE;
BEGIN
SELECT group_list.group_id
INTO v_group_id
FROM group_list
WHERE group_list.groupname = p_groupname;
RETURN v_group_id;
END get_group_id;
/
and I am doing simple update command to the table, but its giving me ORA-04091 error. Any help is appreciated
update GROUP_LIST set MGR_GROUP_ID = get_group_id('manager1') where group_id = get_group_id('employee51');
Error which I am getting is -
Error report:
SQL Error: ORA-04091: table is mutating, trigger/function may not see it
ORA-06512: at "GET_GROUP_ID", line 7
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.