Can't update a Task if auditing a custom field, System Error

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

Auditing a custom field on the Task object, and now unable to update / save a Task, error message:

ORA-04088: error during execution of trigger 'NIKU.T_TASK_OODF_CA_TASK'

When updating the task in Clarity, an error is thrown: System error. Contact system administrator.

Error message in app-ca.log:
Caused by: java.sql.SQLException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "NIKU.ODF_AUD_5001883_FCT", line 5
ORA-06512: at "NIKU.T_TASK_OODF_CA_TASK", line 12
ORA-04088: error during execution of trigger 'NIKU.T_TASK_OODF_CA_TASK'

When we uncheck the field from auditing, we are able to save tasks successfully. 

Environment

Release : All Supported Clarity releases

Resolution

Workaround: Remove the custom field audited from the Task Object - Audit

Documented in DE55402(Not a Bug)

The audit function tries to fetch the old value and new value based on the lookup query. In this case, because the query is returning multiple values, audit creation is failing. 

Below is the example database function responsible to fetch the value. This function gets created when we enable the audit for this field for the first time. You can update this function directly on the database by adding a DISTINCT keyword, and this should solve the problem. Try this on a Test environment first. 

Note: The function will be different for every different dynamic query, so this is an example:

create or replace FUNCTION ODF_AUD_5001883_FCT (P_VALUE IN VARCHAR2)
RETURN VARCHAR2 IS V_RETURN_VALUE VARCHAR2(4000);
 pragma AUTONOMOUS_TRANSACTION;
BEGIN
 select project_name into V_RETURN_VALUE from (SELECT DISTINCT
 imp.code project_code
,imp.name||' ('||imp.code||')' project_name
FROM INV_INVESTMENTS INV
JOIN INV_PROJECTS IPRJ ON IPRJ.PRID=INV.ID 
JOIN PRTASK TSK ON TSK.PRPROJECTID=INV.ID
JOIN (
    SELECT
         MAX(ID) ID
        ,ODF_PARENT_ID
        ,TEST_RELEASE
    FROM ODF_CA_TEST_RELEASE
    GROUP BY ODF_PARENT_ID
        ,TEST_RELEASE
)PRJR ON PRJR.test_release=INV.code 
JOIN INV_INVESTMENTS IMP ON IMP.ID=PRJR.ODF_PARENT_ID
WHERE 1=1
 
AND 1=1) l where project_code=P_VALUE;
RETURN V_RETURN_VALUE;
END;