Go up

Next thread:	which redolog file member is read
Prev thread:	Identifying procedure dependencies

Message	Score	Author	Date
Hi, my purpose is to check duplication in a ta......		ajay cheble	Apr 15, 2005
Your trigger "DISC.SYSTEM_SHORT_DESC_TRIG" contain......		LKBrwn DBA>	Apr 15, 2005
Yes. I know that .. am just asking the work around......		ajay cheble	Apr 15, 2005
Hi ajay, don't complicate too much, create uniq......		Ales Kavsek	Apr 15, 2005
Hi Ales, Thank you for your reply. To tell you ......		ajay cheble	Apr 16, 2005
Hi Ajay, sorry, I was not clear enough in my pr......		Ales Kavsek	Apr 16, 2005
Hey man too good.. thank you.. am just too dumb......		ajay cheble	Apr 17, 2005

Follow up by mail

Click here

---

Subject:	mutating problem
Author:	ajay cheble, India
Date:	Apr 15, 2005, 1971 days ago
Os info:	linux
Oracle info:	9i
Error info:	SQL> UPDATE system_details SET SYSTEM_SHORT_DESC ='A_TEST ' 2 WHERE SYSTEM_ID = 41; UPDATE system_details SET SYSTEM_SHORT_DESC ='A_TEST ' * ERROR at line 1: ORA-04091: table DISC.SYSTEM_DETAILS is mutating, trigger/function may not see it ORA-06512: at "DISC.CHECK_SYSTEM", line 5 ORA-06512: at "DISC.CHECK_SYSTEM", line 8 ORA-06512: at "DISC.SYSTEM_SHORT_DESC_TRIG", line 2 ORA-04088: error during execution of trigger 'DISC.SYSTEM_SHORT_DESC_TRIG'
Message:	Hi, my purpose is to check duplication in a table called as system_details on the field system_short_desc. Pls see the code below UPDATE system_details SET SYSTEM_SHORT_DESC ='A_TEST ' WHERE SYSTEM_ID = 41; create or replace trigger system_short_desc_trig before insert or update of system_short_desc on system_details for each row begin check_system(:new.system_short_desc); end; / create or replace procedure check_system(var_system_short_desc system_details.system_short_desc%type) as TYPE vc_array IS TABLE OF VARCHAR2(2000); temp vc_array; i number:=1; cursor c1 is select system_short_desc from system_details; r1 c1%rowtype; begin open c1; loop fetch c1 into r1; exit when c1%notfound; dbms_output.put_line(i); temp(i):=r1.system_short_desc; i:=i+1; end loop; close c1; loop exit when i=0; if upper(ltrim(rtrim(var_system_short_desc))) = upper(ltrim(rtrim(temp(i)))) then raise_application_error('-20202','cannot insert duplicate system short description'); end if; i:=i-1; end loop; end; / Pls help. regards, Ajay C
Goto:	Reply - Top of page

If you think this item violates copyrights, please click here

---

Subject:	Re: mutating problem
Author:	LKBrwn DBA>, United States
Date:	Apr 15, 2005, 1971 days ago
Message:	Your trigger "DISC.SYSTEM_SHORT_DESC_TRIG" contains DML on the same table it is "firing" upon -- remove the offending DML.
Your rating?:	This reply is Good Excellent
Goto:	Reply - Top of page

If you think this item violates copyrights, please click here

---

Subject:	Re: mutating problem
Author:	ajay cheble, India
Date:	Apr 15, 2005, 1971 days ago
Message:	Yes. I know that .. am just asking the work around how do i get rid of this and achieve my purpose to prevent user inserting/updating a same system_short_desc in the system_details table. Would be thankful if you cud address this also. Thanks for your reply. Ajay Cheble
Your rating?:	This reply is Good Excellent
Goto:	Reply - Top of page

If you think this item violates copyrights, please click here

---

Subject:	Re: mutating problem
Author:	Ales Kavsek, Slovenia
Date:	Apr 15, 2005, 1971 days ago
Score:
Message:	Hi ajay, don't complicate too much, create unique index on system_short_desc and you're done. Even if your original solution would work (let's discard mutating trigger problem and unique index as an obvious solution), you made every effort to make check_system procedure unscalable (for example if you have an idea to call this procedure outside trigger, don't do it!). Why do you need array for? And two loops? All this hassle for just one select (if you can't use unique index for some reason - not likely ;-) that must return 0 or n >=1 if it finds the description that user already entered? Regards, ales
Your rating?:	This reply is Good Excellent
Goto:	Reply - Top of page

If you think this item violates copyrights, please click here

---

Subject:	Re: mutating problem
Author:	ajay cheble, India
Date:	Apr 16, 2005, 1971 days ago
Message:	Hi Ales, Thank you for your reply. To tell you something nice.. I already have a unique index on the system_short_desc. But unique doesnt distinguish between 'ajay' and 'AJAY', and this is what I do not want to happen. Here wud be duplication that my table wont recognise.. and thats why all this hard work. If you can think beyond this please do let me know. regards, Ajay Cheble.
Your rating?:	This reply is Good Excellent
Goto:	Reply - Top of page

If you think this item violates copyrights, please click here

---

Subject:	Re: mutating problem
Author:	Ales Kavsek, Slovenia
Date:	Apr 16, 2005, 1970 days ago
Score:
Message:	Hi Ajay, sorry, I was not clear enough in my previos post. Let's take a look at example: SQL> create table mytable (short_desc varchar2(10)); Table created. SQL> create unique index mytab_short_desc_uq on mytable (UPPER(short_desc)); Index created. SQL> SQL> insert into mytable values('ajay'); 1 row created. SQL> SQL> insert into mytable values('AJAY'); insert into mytable values('AJAY') * ERROR at line 1: ORA-00001: unique constraint (KAVSEK.MYTAB_SHORT_DESC_UQ) violated Regards, Ales
Your rating?:	This reply is Good Excellent
Goto:	Reply - Top of page

If you think this item violates copyrights, please click here

---

Subject:	Re: mutating problem
Author:	ajay cheble, India
Date:	Apr 17, 2005, 1969 days ago
Message:	Hey man too good.. thank you.. am just too dumb i think.. and you need not be sorry when you are helping some one. thank you very much, it worked. ajay
Your rating?:	This reply is Good Excellent
Goto:	Reply - Top of page

If you think this item violates copyrights, please click here