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 [1].
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 [2].
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 [3].
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!
Leave a Reply to Damith Jinasena Cancel reply