PMO Upgrade fails on ODF_PROJECT_V or ODF_PROJECT_V2, ORA-04063 - view has errors
Article ID: 125140
Clarity PPM SaaSClarity PPM On Premise
When applying PMO, error similar to the one below: [exec] [exec] Error Applying XOG: Failure occurred while applying queries/cop_projectKPIHierarchy.xml [exec] [exec] Check /fs0/clarity1/clarity/logs/content/xog/csk/queries/cop_projectKPIHierarchy_out.xml for errors [exec] [exec] Failed to install content pack. ID: csk: exec returned: 2 [exec] [exec] Failed to install content pack. ID: csk: exec returned: 2 [exec] [exec] Error occurred: /fs0/clarity1/clarity/META-INF/content/xog/csk/install.xml:242: exec returned: 2 [exec] [exec] Check admin.log or use -verbose for more information.
From the actual out file:
<Description>XOG-3052: Query record has not been inserted com.niku.union.persistence.nsql.NSQLSyntaxException: NSQL Syntax exception: NSQL_SYNTAX_ERROR_DB_ERROR, Info: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-04063: view "PPM.ODF_PROJECT_V2" has errors
DBA to recompiled and this the problem is that ODF_PROJECT_V has errors:
Investigated the reason ODF_PROJECT_V does not compile with error: ORA-00904: "ODF_CA_PROJECT"."Z_FIELD": invalid identifier
The issue is with the number of custom attributes.
The number of custom attributes can be checked with SQL query: select count(*) from odf_custom_attributes where object_name in('project','inv')
This total has to be reduced to under 500. We recommend the number of custom attributes to be kept under 100 per object as best practice, and the maximum supported is 500. Objects as project and investments should not exceed 500 as a total.
If you need more attributes, please add custom objects on the project object to hold them.
This is not only PPM but Oracle limitation. We use views to keep the columns on each object, and Oracle can only handle a limited amount of columns. They should not be over 500.