2021 could be the year where most people are discussing about mutations due to Covid 19, but we are dealing with mutations since long time 🧐. If you have been struggling with below error, you know what I’m talking about and this post might be useful for you!
A mutating table (ORA-04091) error occurs when underlying oracle trigger for custom event attempts to read or write the table which the trigger was fired.
When doing IFS configurations, there are several tricks we can use to avoid the mutating table error and each has pros and cons. In this post, I demonstrate several such methods with some examples.
1. Use AUTONOMOUS_TRANSACTION Pragma
When you add AUTONOMOUS_TRANSACTION pragma in your code, that code block works independently from the main transaction and therefore helps to avoid mutating error if the table mutation caused just because you are trying to access data in the same row, but not affected by the trigger.
Suppose you want to implement an event to block adding new customer orders when there are orders in Planned status exist for the customer. Typically you would write an event on LU CustomerOrder, Table CUSTOMER_ORDER_TAB for new objects are created. My event action looks like below.
DECLARE contract_ VARCHAR2(100); order_no_ VARCHAR2(100); customer_no_ VARCHAR2(100); cnt_ NUMBER := 0; CURSOR get_orders_planned IS SELECT count(1) FROM CUSTOMER_ORDER WHERE CONTRACT = contract_ AND customer_no_ = customer_no_ AND OBJSTATE = 'Planned'; BEGIN contract_ := '&NEW:CONTRACT'; customer_no_ := '&NEW:CUSTOMER_NO'; OPEN get_orders_planned; FETCH get_orders_planned INTO cnt_; CLOSE get_orders_planned; IF cnt_ > 0 THEN ERROR_Sys.Appl_General('CustomerOrder', 'CPLANNEDORDEXISTS: There are :P1 Planned orders exist. New orders are not allowed.', cnt_); END IF; END;
This would result in Table mutation since our cursor is selecting data from CUSTOMER_ORDER view which is based on CUSTOMER_ORDER_TAB which is our event is based on.
Same code with AUTONOMOUS_TRANSACTION pragma looks like below.
DECLARE PRAGMA AUTONOMOUS_TRANSACTION; contract_ VARCHAR2(100); order_no_ VARCHAR2(100); customer_no_ VARCHAR2(100); cnt_ NUMBER := 0; CURSOR get_orders_planned IS SELECT count(1) FROM CUSTOMER_ORDER WHERE CONTRACT = contract_ AND customer_no_ = customer_no_ AND OBJSTATE = 'Planned'; BEGIN contract_ := '&NEW:CONTRACT'; customer_no_ := '&NEW:CUSTOMER_NO'; OPEN get_orders_planned; FETCH get_orders_planned INTO cnt_; CLOSE get_orders_planned; IF cnt_ > 0 THEN ERROR_Sys.Appl_General('CustomerOrder', 'CPLANNEDORDEXISTS: There are :P1 Planned orders exist. New orders are not allowed.', cnt_); END IF; END;
AUTONOMOUS_TRANSACTION pragma works well if your depending code segment is a select statement. But, if you need to perform any Create, Update, Delete operations, trigger will throw ORA-06519: active autonomous transaction detected and rolled back error since the operation inside the autonomous transaction is not committed.
This can be avoided by adding a COMMIT; statement at the end of the PL/SQL block. Some example coding can be found in the IFS Community .
Be extra cautious when you need to perform commit inside event action with AUTONOMOUS_TRANSACTION pragma since if the event action logic tries to modify the same record, it will end up with ORA-00060: deadlock detected while waiting for resource error.
2. Use Background Processing
Background processing is the safest method if you can find the right PLSQL procedure to execute and it supports background processing.
IFS framework has Transactions_SYS.Deferred_Call() procedure to submit background jobs and it can call a database procedure which takes normal input arguments, IFS attr or IFS Message type. Some good examples for this can be found in the IFS Community .
However there some limitations with Transactions_SYS.Deferred_Call such as
- You can’t call a function or a procedure with IN OUT type parameters
- Input parameter type cannot be Clob/Blob
In most of the times we need to do execute our own logic, not just one IFS standard procedure call, therefore the usage of background processing is limited in that aspect. Creating a customized procedure is the ‘clean’ way to proceed in this scenario but there’s a quick and dirty fix where it’s possible to execute a custom script as a background job using Fnd_Event_Action_API.Action_Executeonlinesql method. Now I’m tend to use this method more and more in my events and it’s proven to work! Nice example of usage of this can be found here .
DECLARE attr_ VARCHAR2(32000); sql_msg_ VARCHAR2(32000); stmt_ VARCHAR2(32000); job_id_ NUMBER; error_text_ VARCHAR2(2000); BEGIN stmt_ := ' DECLARE BEGIN Write whatever the code you want here. END;'; sql_msg_ := Message_SYS.Construct('UPD'); Message_SYS.Add_Attribute(sql_msg_, 'SQL', stmt_); Client_SYS.Clear_Attr(attr_); Client_SYS.Add_To_Attr('SQL_DATA_', sql_msg_, attr_); Client_SYS.Add_To_Attr('MSG_', '', attr_); Transaction_SYS.Deferred_Call(job_id_,'Fnd_Event_Action_API.Action_Executeonlinesql','PARAMETER',attr_,'Set null Customer Address'); END;
3. Use a Related Record as the Trigger
Sometimes you could find a related record update in relation to the main record update.
Eg: History record is crated for major updates of the customer order
If you can find such a relative record which can refer the main record by keys, then you can add the event on the other record instead of the main record and there’s no problem with accessing data in the main record. But still you cannot modify the record since it’s on same transaction.
Above are the main methods I’m using in my coding. If you know some other hacks that you are using to avoid mutating table error, please add them in the comments section!
Thank you Damith. It worked and very help us 🙂
Glad that it helped you 😊
Hi Damith, this reply is very late to the party but I just want to say that I’ve just written my very first custom object for IFS and could not have done so without your work on this blog and the user forum.
Thank you so much for sharing your knowledge in such an accessible way, you are excellent.
Thanks a lot for your appreciation. Glad to know it helped you 🙂
Thanks for the explanation.
I have a similar background process but the solution isn’t working.
I’m creating Quick Reports through a DATA MIG JOB.
In Quick Report Query column is CLOB data type column, it won’t support including the source column in DATA MIG TOOL.
Then thought of writing an event/action to write the Query column.
Event Table – QUICK_REPORT_TAB
my Event action,
stmt_ := ‘declare
table_prefix_ VARCHAR2(100) := NULL;
truncated_ VARCHAR2(100) := NULL;
CURSOR get_qr_obj IS
SELECT objid, objversion
WHERE quick_report_id = report_id_;
report_id_ := ”&NEW:QUICK_REPORT_ID”;
tclass_ := ”&NEW:COMMENTS”;
fetch get_qr_obj into objid_, objversion_;
— IF (Quick_Report_API.Exists(report_id_) = TRUE) THEN
Technical_Specification_API.Make_Select_Statement(tclass_, table_prefix_, truncated_, stmt_);
Quick_Report_API.Write_Query__(objversion_, objid_, stmt_);
sql_msg_ := Message_SYS.Construct(‘UPD’);
Message_SYS.Add_Attribute(sql_msg_, ‘SQL’, stmt_);
Client_SYS.Add_To_Attr(‘SQL_DATA_’, sql_msg_, attr_);
Client_SYS.Add_To_Attr(‘MSG_’, ”, attr_);
‘Update created QR reports Query column.’);
What could be the reason, greatly appreciate your comment?
Sorry for the late reply. What’s the error you get in the background job? What I usually do is to copy the SQL script from the posted background job and run it manually in PLSQL to check the errors.
Thank you 🙂
Thanks for the blog Damith! I am getting an error message on the background job “Argument SQL_DATA_ must have a value, because default value do not exist.” do you know what is causing this?
Could be something with the variables. Can you post a code how you assign values to the sql_msg_ variable?
Damith, it was my mistake. I was using stmt_ instead of attr_ as the value for SQL_DATA_. please disregard, and thanks again for your excellent blog
This was really helpful. Thanks Damith! You’re the man.
Nice to hear you like the blog 🙂
This was very helpful!
Nice One !
Good stuff brother!