Receive ‘System Error’ when ‘Solution Owner’ attribute is audited for Changes
When logs were pulled we saw below error basically indicating that it is not accepting the values that are being passed to the audit trigger.
ERROR 2018-11-16 20:15:56,513 [http-nio-80-exec-75] niku.xql2 (clarity:admin:session_id:projmgr.projectPropertiesPost) Internal Processing exception
SQL error code: 1422
Error message: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "YOUR_SCHEMA.ODF_AUD_5001168_FCT", line 5
ORA-06512: at "YOUR_SCHEMA.T_PROJECT_OODF_CA_PROJECT", line 291
ORA-04088: error during execution of trigger 'YOUR_SCHEMA.T_PROJECT_OODF_CA_PROJECT'
We started looking at ODF_AUD_5001168_FCT which is function that is getting created by the system any time you add an attribute to be audited. We then compared the SQL that was in the function versus the NSQL behind the lookup that is tied to "Solution Owner" attribute, that lookup is also called "Solution Owner" but the NSQL in there is slightly different then what it had in the function which is when attribute was originally audited (first time) so what that tells us that someone modified the lookup after it was already tied to the attribute but when you do that the function that is responsible for audit does not get updated as we do not allow for modification of the lookup once it is tied to an attribute but I am assuming this was done by XOG. What we did next was we created a new lookup using the exact same NSQL which is behind the Lookup: Solution Owner and tied to new attribute and had no issues with auditing. Since we can't update function we only have one option and that would be for customer to create another attribute and then you could do a gel script to copy data over from an old one to new one and continue with one attribute going forward.