Home -> Usenet -> comp.databases.oracle -> table TEST is mutating, trigger/function may not see it

table TEST is mutating, trigger/function may not see it

From: HArry <hxsingh_at_duke-energy.com>
Date: 4 Sep 2002 15:08:41 -0700
Message-ID: <dafb8e6.0209041408.16484fe6_at_posting.google.com> 






I have two tables:Test and test2. 


Table structue:

 TNO                                      NUMBER(3)
 TNAME                                    VARCHAR2(20)


I need to insert into test by selecting TNAME from test2 but TNO
should be the max+1 .For selecting max of tno I have a function
:getmaxTno as



create or replace function getmaxTno returns int as
v_max number:=0;


begin


select max(tno) into v_max from test;


return(v_max) ;


end;




for bulk inserting into test I have a procedure insertTest as



create or replace procedure insertTest  as
v_num number:=0;


begin


insert into test select getmaxTno+1,tname from test2 ;
end;




when I execute the stored proc I get the error:
ORA-04091: table INT.TEST is mutating, trigger/function may not see it
Any help would be greatly appreciated.


Thanks in advance.
Received on Thu Sep 05 2002 - 00:08:41 CEST












Original text of this message