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

book

Article ID: 125140

calendar_today

Updated On:

Products

CLARITY PPM FOR ITG CLARITY PPM FEDERAL Clarity PPM SaaS - Application Clarity PPM On Premise

Issue/Introduction

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
 

Cause

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')
 

Environment

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

Resolution

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.