469,301 Members | 2,282 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,301 developers. It's quick & easy.

Mutating Error in Triggers

151 100+
Can Someone help me how to avoid mutating error while using triggers.


I work on oracle apps. I created a vacation rule such that responsibility is delegated to other person. These details are captured in wf_routing_rules.

I created a procedure that takes the values from wf_routing_rules and sends mail using utl_smtp. I created a trigger such that for every row insert in wf_routing_rules, this procedure is called.

I know that doing a transaction and trigger on same table wil give mutating error.

Is there a way to avoid this ( other than changing the table name).

Glad if there is a different solution for this too.

Vamsi
Mar 5 '09 #1
16 6762
vamsioracle
151 100+
I tried by using Pragma autonomous_transaction.

now i get an error

java.sql.SQLException: ORA-01403: no data found
ORA-01403: no data found
ORA-06512: at "APPS.CBTT_DELEGATE_RULE_PROC", line 19
ORA-06512: at "APPS.CBTT_DELEGATE_RULE_TRIGGER", line 7
ORA-04088: error during execution of trigger 'APPS.CBTT_DELEGATE_RULE_TRIGGER'
ORA-06512: at line 6


Is the trigger firing even before insert . The above error is on the apps screen,vacation rule creation.

vamsi
Mar 5 '09 #2
amitpatel66
2,367 Expert 2GB
I assume your trigger is after insert trigger. I suggest you to use :new values in the trigger and pass them as a parameter to the PROCEDURE instead of useing a SELECT query in the PROCEDURE.

AFTER INSERT, just pick the data from the table using :new clause and pass them as input parameter to the procedure you are calling, and do the rest.
Mar 10 '09 #3
vamsioracle
151 100+
Thanks dude

but it is better if i post my code.

----------------------------
Code:

create or replace trigger xx_routing
after insert
on wf_routing_rules
for each row
begin


--- here i call the procedure that has the following code-------

select role,attribute into x,y from wf_routing_rules where begin_date >= to_date(sysdate);

utl_smtp.conn(host,port)
--------

end procedure;
--------------------

So if you can observe the trigger and the procedure uses the same table.I dont have any other chance. As soon a row is inserted in that table , i must capture that row and send through utl_smtp.

is there any other solution for this.

vamsi
Mar 11 '09 #4
vamsioracle
151 100+
Thanks dude

but it is better if i post my code.

----------------------------
Code:

create or replace trigger xx_routing
after insert
on wf_routing_rules
for each row
begin


--- here i call the procedure that has the following code-------

select role,attribute into x,y from wf_routing_rules where begin_date >= to_date(sysdate);

utl_smtp.conn(host,port)
--------

end procedure;
--------------------

So if you can observe the trigger and the procedure uses the same table.I dont have any other chance. As soon a row is inserted in that table , i must capture that row and send through utl_smtp.

is there any other solution for this.

vamsi
Mar 11 '09 #5
amitpatel66
2,367 Expert 2GB
Try this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. create or replace trigger xx_routing
  3. after insert
  4. on wf_routing_rules
  5. for each row
  6. begin
  7.  
  8. proc1(:new.role,:new.attribute);
  9.  
  10. --- here i call the procedure that has the following code-------
  11.  
  12. /*select role,attribute into x,y from wf_routing_rules where begin_date >= to_date(sysdate);*/
  13. --now do what ever you want to with the input parameters rolw and attribute
  14.  
  15. utl_smtp.conn(host,port)
  16. --------
  17.  
  18. end procedure;
  19.  
Mar 12 '09 #6
madankarmukta
308 256MB
@amitpatel66
Hi amit,

I have a doubt over your solution.Kindly correct me if I am getting wrong.

U did the desired operation of selecting the row within the procedure which finally refers to wf_routing_rules.will that solve the problem of mutation..?

If that's true we can put "selecting the row " into child transaction i.e. use of autonomous transaction may solve the problem; as per my understanding.

Thanks!
Mar 12 '09 #7
vamsioracle
151 100+
Dude

I tried autonomous transaction too , it didn't work. Let me try this new solution and get back.
Mar 12 '09 #8
Jibran
30
Refer to the following link: http://www.dba-oracle.com/t_avoiding...able_error.htm, it has some good suggestions to prevent mutating errors.
Mar 12 '09 #9
amitpatel66
2,367 Expert 2GB
@madankarmukta,

I have commented the SELECT statement that is used in the Procedure by the @OP. That select statement is causing all the problem. Now the @OP needs the values of the latest record inserted in to table so that can be accessed just by using :NEW clause in the TRIGGER and pass those values as an INPUT parameter from a TRIGGER to a PROCEDURE.

I hope I cleared your doubt!!
Mar 13 '09 #10
vamsioracle
151 100+
It didn't work for me either.

Can we try in some other way. My requirement is as soon as end user creates the vacation rule in oracle apps, My procedure has to run. So can we call this procedure in the vacation rules.
Mar 14 '09 #11
amitpatel66
2,367 Expert 2GB
Why dont you try using FAST FORMULA?
Mar 16 '09 #12
vamsioracle
151 100+
Instead,

I will create the procedure and register in Apps as concurrent program. And for each row insert in the table i will call this concurrent program. I hope this will not create any mutating error again

If yes: how to call the concurrent program from trigger.
Mar 16 '09 #13
amitpatel66
2,367 Expert 2GB
My friend you are complicating things here.

You can use FND_REQUEST.SUBMIT_REQUEST API to submit a concurrent program programatically.

Try using FAST FORMULA where you can call your procedure as well from there. R12 HRMS provides very good feature of FAST FORMULA
Mar 16 '09 #14
Saii
145 Expert 100+
What about the documented method of using 3 triggers to avoid mutating errors.
Before insert for each row to set package variable(array).
after insert for each row to get the unique id of rows inserted and store sin package array
after insert statement level- to do whatever procesing u want for the rows in packaged array

This doesnt work???
Apr 17 '09 #15
vamsioracle
151 100+
thanks all

it worked for me.

I created a after insert trigger and used pragma autonomous transaction, from there i had to call procedure that has the logic and one more procedure that from here, to send a mail.

for some reason, it worked only if the calling is indirect (calling one procedure from other)


vamsi
Apr 20 '09 #16
@amitpatel66
My question is :
1. can we Store all records of column from table on which trigger is written ????
e.g. here in "wf_routing_rules" this table can i get all role list which is stored in array and pass as an parameter to Procedure !!

Thanx !
Jan 10 '12 #17

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by robert | last post: by
4 posts views Thread by M | last post: by
4 posts views Thread by M Mueller | last post: by
2 posts views Thread by Karl | last post: by
1 post views Thread by mask | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.