PMO Upgrade fails on ODF_PROJECT_V or ODF_PROJECT_V2, ORA-04063 - view has errors


Article ID: 125140


Updated On:


Clarity PPM SaaS Clarity 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')


Release: CODFSS99000-15.4.1-PPM SAAS FedRAMP-Sandbox-Small Environment


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.