Datamart rollup failure: ORA-06502: PL/SQL: numeric or value error

Products

• Clarity PPM On Premise

Issue/Introduction

The Datamart Rollup jobs fails since upgrading to 16.1.1.

The bg-ca.log file shows the following error:

ERROR 2023-04-24 05:15:34,144 [Dispatch Datamart Rollup - Time Facts and Time Summary : bg@<hostname>(tenant=clarity)] niku.njs (clarity:admin:89577383__952FCCC8-373C-4A97-9E5E-E10EDBEB5C24:Datamart Rollup - Time Facts and Time Summary) Error executing job: 5835179 error java.sql.SQLException: ORA-20000: Error in CMN_JOB_DATAMART_ROLLUP_SP - ORA-20000: Error in NBI_EXTRACT_TF_TS_SP - Rolling up NBI_PM_PROJECT_TIME_SUMMARY: ORA-20000: Error in NBI_PM_PROJECT_TIME_SUMMARY - Adding Indices back on the NBI_PM_PROJECT_TIME_SUMMARY table: ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "NIKUP.CMN_JOB_DATAMART_ROLLUP_SP", line 11
ORA-06512: at line 1

Environment

Clarity 16.1.1

Cause

Found that indices were dropped and no indices were being added by the job to the following tables.

NBI_FM_PROJECT_TIME_SUMMARY

NBI_PM_PROJECT_TIME_SUMMARY

NBI_RESOURCE_TIME_SUMMARY

There are multiple PPM schemas on the same DB and the SPs involved do not specify the schema and hence fail.

Resolution

The following line of the SPs has been modified:

From:

aic.index_name = ui.index_name

To:

aic.index_name = ui.index_name and aic.table_owner='XXXXX'

Specifying the schema fixed the issue and the job is running successfully.