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

The mutating table error tells you that you are trying to select from a table that is currently being modified by an insert, delete or update statement. This is exactly your case. You most often see this error in a trigger environment.

It is not clear at all what you are trying to do. The following is my guess of what you want to do:

SQL> select * from tyu;

       TNO TNAME

---------- --------------------
        10 f
        11 g

SQL> select * from tyu2;

       TNO TNAME

---------- --------------------
         3 a
         4 b
         5 c

SQL> insert into tyu select * from ( select max(tno)+1 from tyu), (select tname from tyu2);

3 rows created.

SQL> select * from tyu;

       TNO TNAME

---------- --------------------
        10 f
        11 g
        12 a
        12 b
        12 c

SQL> hxsingh_at_duke-energy.com (HArry) wrote in message news:<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 - 20:23:07 CEST