Search from 700 + Posts

Nov 16, 2009

ORA-01422: exact fetch returns more than requested number of rows inPackage OE_Line_Fullfill Procedure Get_Activity_Result

This is bit Intrestng Error.It is very easy to guess from the Error name/message that it is because our query in pl/sql code is returning more than 1 row and expecation is query should return only one Row.
On My Analysis I found that this Error appears when we have duplicate data in wf_item_activity_statuses.Let me be honest this is very unrealistic situation to have duplicate data in this table unless some one really took this table for ride(and most common reason is when user try to retry/skip workflow activity from the workflow admin). So to avoid this explain your end use not to skip/retry workflow activity from workflow admin , rather talk to IT department.


Now best option to fix this is Delete duplicate data ..

2 comments:

  1. Can you please explain the root cause for the issue and how it can be resolved?
    I need to understand , because we have workflow implemented in our system and we do need to use the retry activity often.

    ReplyDelete
  2. Hi

    As I have stated that you can see duplicate data in this table only because of corruption , but it is hard to say that what is the reason for the corruption , but based on feedback from many customer I have concluded that RETRY/SKIP is one of reason for data corruption.

    So if your user are doing it frequently then ask them not to do that.SKIP/RETRY is ok once in a while to resolve the issues( and progress the flow) , but on regular basis it is not good.

    Hope that helps.

    ReplyDelete