Mutating Error in Triggers

[vamsioracle]

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

[vamsioracle]

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

[amitpatel66]

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.

[vamsioracle]

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

[vamsioracle]

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

[amitpatel66]

Try this:

 
create or replace trigger xx_routing
after insert
on wf_routing_rules
for each row
begin
 
proc1(:new.role,:new.attribute);
 
--- 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);*/
--now do what ever you want to with the input parameters rolw and attribute
 
utl_smtp.conn(host,port)
--------
 
end procedure;
 

[madankarmukta]

@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!

[vamsioracle]

Dude

I tried autonomous transaction too , it didn't work. Let me try this new solution and get back.

[Jibran]

Refer to the following link: http://www.dba-oracle.com/t_avoiding...able_error.htm, it has some good suggestions to prevent mutating errors.

[amitpatel66]

@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!!

[vamsioracle]

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.

[amitpatel66]

Why dont you try using FAST FORMULA?

[vamsioracle]

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.

[amitpatel66]

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

[Saii]

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???

[vamsioracle]

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

[swapnilbunty123]

@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 !