ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

Load DWH job fails with error "ORA-06502" on DWH_INV_ASSIGN_SUM_FACTS_LOAD or DWH_INV_TASK_SUM_FACTS_LOAD

book

Article ID: 101868

calendar_today

Updated On:

Products

Clarity PPM SaaS Clarity PPM On Premise

Issue/Introduction

On running Load Data Warehouse job, Full or Incremental, the following error is thrown every time:

ClarityDB - isOracle? - An error occurred executing this job entry :
Couldn't execute SQL: BEGIN
 DWH_INV_ASSIGN_SUM_FACTS_LOAD
  (
  P_DBLINK => 'CLARITYDBLINK',
  P_LAST_LOAD_DATE => to_date('1910/01/01 00:00:00', 'yyyy/MM/dd HH24:mi:ss'),
  P_CURRENT_DIM_LOAD_DATE => to_date('2018/06/04 13:31:43', 'yyyy/mm/dd HH24:MI:SS'),
  P_ARRAY_SIZE => 50000
  );
END;
 
[CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_INV_ASSIGN_SUMMARY_FACTS. SQLERRM : ORA-06502: PL/SQL: numeric or value error: Bulk bind: Error in define
ORA-06512: at "CLARITY154DWH.DWH_INV_ASSIGN_SUM_FACTS_LOAD", line 66
ORA-06512: at line 2

Cause

DE41741 - Entering a very high value on an assignment in Clarity is allowed. An invalidate entry (over 1,000,000,000,000,000) was entered for ETC/Pending ETC/Pending Actuals or any other value on the assignment. We do not recommend entering impossible values as this is not how the product is meant to be used. In some cases, for example, you may enter billions for ETC which is not possible to happen in a single life span.

Environment

Component: Data Warehouse

Resolution

Fixed in Clarity 15.5. The fix will ensure that all invalid entries that go over will be set to 0 in DWH. This way the job will no longer fail if a user enters an invalid entry.

Workaround:

  1. Query the PRASSIGNMENT table and look for any values that are going outside the limits such as
  2. 1,000,000,000,000,000
  3. Once you find them, correct the value for this assignment from CA PPM UI
  4. Run Load Data Warehouse job - Full

Additional Information

Reference also: