Load Data Warehouse job fails with ORA-06502: PL/SQL: numeric or value error: Bulk bind: Error in define (Oracle only)

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Load Data Warehouse job Full or Incremental, when ran, fails with error message:
 
ClarityDB - isOracle? - An error occurred executing this job entry :
Couldn't execute SQL: BEGIN
 DWH_INV_ASSIGN_SUM_FACTS_LOAD
  (
  P_DBLINK => 'MYDBLINK',
  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
 
 
Or
 
ClarityDB - isOracle? - An error occurred executing this job entry :
Couldn't execute SQL: BEGIN
DWH_INV_TASK_SUM_FACTS_LOAD (P_ARRAY_SIZE => 50000);
END;
 
[CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_INV_TASK_SUMMARY_FACTS. SQLERRM : ORA-06502: PL/SQL: numeric or value error: Bulk bind: Error in define
ORA-06512: at "CLARITY154DWH.DWH_INV_TASK_SUM_FACTS_LOAD", line 48
ORA-06512: at line 2
 
 

Cause

Data Warehouse has data restrictions to ensure for the data integrity. Sometimes a value entered by a user can be going outside of the bounds.
 

Environment

Release:
Component: ODDWH

Resolution

DE41741 Entering a very high value on an assignment in PPM is allowed, then fails Load Data Warehouse job with ORA-06502: PL/SQL: numeric or value error: Bulk bind: Error in define
Fixed in 15.5
The fix would be that any PRassignment values that go over 1,000,000,000,000,000 would be set to 0 in DWH since it’s an invalid number.

DE42129 Multiple decimal float number in PRASSIGNMENT can fail Load Data Warehouse job with ORA-06502: PL/SQL: numeric or value error: Bulk bind: Error in define
CNR on 15.4.1


Workaround:
Find the value that goes over bounds and correct it, then run a Load Data Warehouse - Full Load


1.    To identify the results, run the job again as Full Load until it fails. Pause the DWH jobs.

2.    Run this query on the DWH database (ensure you fill in the correct DBLINK name)

select assignment_key from [email protected]MYDBLINK
minus
select assignment_key from DWH_INV_ASSIGN_SUMMARY_FACTS

3.    Review the results in Excel. Anything that goes over 1,000,000,000,000,000 or has more than 35 numbers after the delimiter is suspicious.

4.    Once you identify the issue, find out what is the Project/Task the assignment is on with query:

select i.code "ProjectCode", i.name "ProjectName", t.prname "taskName", a.prresourceid "AssignedResourceID" from prtask t, inv_investments i, prassignment a
where t.prprojectid = i.id
and a.prtaskid =t.prid
and a.prid =<enter the exact assignment key from step 2 that you identified as an issue>

5.    Connect to UI, find the Project, Task and Assignment and correct the value manually.
6.    Save
7.    Run Load Data Warehouse - Full