Data Warehous Load fails with ORA-01722: invalid number

Products

• Clarity PPM SaaS

Issue/Introduction

The Load Data warehouse job fails with the following error message;

ERROR 2020-02-11 04:44:32,642 [Thread-116393] dwh.event ClarityDB - isOracle? - An error occurred executing this job entry : 
Couldn't execute SQL: DECLARE

ERROR 2020-02-11 04:44:32,655 [dwh_etl_interface_dim - User Defined Java Class] dwh.event User Defined Java Class - Unexpected error
ERROR 2020-02-11 04:44:32,657 [dwh_etl_interface_dim - User Defined Java Class] dwh.event User Defined Java Class - java.lang.RuntimeException: ERROR: THERE WERE ERRORS DURING DIMENSION JOB EXECUTION FOR THE TABLE - DWH_INV_PROJECT
 at Processor.processRow(Processor.java:64)
 at org.pentaho.di.trans.steps.userdefinedjavaclass.UserDefinedJavaClass.processRow(UserDefinedJavaClass.java:1181)
 at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60)
 at java.base/java.lang.Thread.run(Thread.java:834)

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION IN DWH_DIM_LOAD (DWH_INV_PROJECT). SQLERRM : ORA-01722: invalid number
ORA-06512: at line 31

Cause

This was caused by attribute that was created on the Project object.

Environment

Release : 15.6.1

Component : CA PPM SAAS APPLICATION

Resolution

Issue was caused by a custom attribute created on Project object.

DWH_CMN_ERROR_MESSAGE table only shows an insert statement into DWH_INV_PROJECT table but does not point out which attribute is causing the job to fail.

Run following sql query to identify the most recently created attribute on Project Object based on the 'created_date' column.

select * from dwh_meta_columns where object_code = 'project' order by created_date DESC

Based on the output look for the Attribute_Code of the latest created attribute and on Project object attribute page search for the attribute and unchecked 
the 'Enable for warehouse' flag. After that run the Load Data Warehouse (in full load).