Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-04091: table is mutating, trigger/function

MDK999Mar 7 2013 — edited Mar 8 2013
I 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.
This post has been answered by dilipkumar10285 on Mar 8 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 5 2013
Added on Mar 7 2013
8 comments
6,879 views