Clarity Upgrade fails on DWH_DROP_CREATE_MATERIALIZED_VIEW_SP with ORA-00972: identifier is too long

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

When running a Clarity upgrade, it fails:

Error on DWH_DROP_CREATE_MATERIALIZED_VIEW_SP

6/12/24 12:08 PM (ExecTask) java.sql.BatchUpdateException: error occurred during batching: ORA-00972: identifier is too long
6/12/24 12:08 PM (ExecTask) 
6/12/24 12:08 PM (ExecTask)     at oracle.jdbc.driver.OracleStatement.executeLargeBatch(OracleStatement.java:4659)
6/12/24 12:08 PM (ExecTask)     at oracle.jdbc.driver.OracleStatement.executeBatch(OracleStatement.java:4527)
6/12/24 12:08 PM (ExecTask)     at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:237)
6/12/24 12:08 PM (ExecTask)     at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
6/12/24 12:08 PM (ExecTask)     at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
6/12/24 12:08 PM (ExecTask)     at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
6/12/24 12:08 PM (ExecTask)     at java.base/java.lang.reflect.Method.invoke(Method.java:568)
6/12/24 12:08 PM (ExecTask)     at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:118)
6/12/24 12:08 PM (ExecTask)     at jdk.proxy2/jdk.proxy2.$Proxy8.executeBatch(Unknown Source)
6/12/24 12:08 PM (ExecTask)     at com.niku.dbtools.Utilities.executeAndCommitSql(Utilities.java:3153)
6/12/24 12:08 PM (ExecTask)     at com.niku.dbtools.DriverApp.apply(DriverApp.java:1500)
6/12/24 12:08 PM (ExecTask)     at com.niku.dbtools.DriverApp.apply(DriverApp.java:1458)
6/12/24 12:08 PM (ExecTask)     at com.niku.dbtools.DriverApp.installSchemaDriver(DriverApp.java:713)
6/12/24 12:08 PM (ExecTask)     at com.niku.dbtools.Utilities.run(Utilities.java:2146)
6/12/24 12:08 PM (ExecTask)     at com.niku.dbtools.Utilities.main(Utilities.java:1161)
6/12/24 12:08 PM (ExecTask) Process - procedure: DWH_DROP_CREATE_MATERIALIZED_VIEW_SP.xml
6/12/24 12:08 PM (ExecTask) 
6/12/24 12:08 PM (ExecTask) SQL Text:
6/12/24 12:08 PM (ExecTask) 
6/12/24 12:08 PM (ExecTask) CREATE OR REPLACE PROCEDURE DWH_DROP_CREATE_MATERIALIZED_VIEW_SP(V_SQL_TEXT IN VARCHAR2) 

Cause

Oracle 19 database still runs on old compatibility parameters

Resolution

1. Rollback the Clarity files and database to prior version
2. Ask the DBA to set all the below parameters accordingly to use Oracle 19c versions on the DWH database
   (example values, they must be Oracle 19 and not Oracle 12):
   • compatible = 19.0.0
   • optimizer_features_enable = 19.1.0
3. Restart the database services
4. Retry the upgrade