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

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

book

Article ID: 103822

calendar_today

Updated On:

Products

Clarity PPM SaaS Clarity PPM On Premise

Issue/Introduction

Load Data Warehouse job (DWH) Full or Incremental, when run, fails with an 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 

Environment

Component: Data Warehouse

Database: Oracle

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.

Resolution

Workaround:

  1. Find the value that goes over bounds and correct it, then run a Load Data Warehouse - Full Load
  2. To identify the results, run the job again as Full Load until it fails. Pause the DWH jobs.
  3. Run this query on the DWH database (ensure you fill in the correct DBLINK name)

    select assignment_key from dwh_inv_assign_sum_facts_v@MYDBLINK
    minus
    select assignment_key from DWH_INV_ASSIGN_SUMMARY_FACTS

  4. 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.
  5. 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>

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

Additional Information

Reference also:

Powered by Wolken Software