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
[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
This was caused by attribute that was created on the Project object.
Release : 15.6.1
Component : CA PPM SAAS APPLICATION
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).