Nika_Hadzhikidi

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

Discussion created by Nika_Hadzhikidi Employee on Jun 29, 2018

Dear community

 

Wanted to share with you an interesting case I got to work with, will also post this as KB, you will be the first to know about it this though! 

 

This is an issue with Load Data Warehouse constraints - it checks the data integrity. I have found in some cases users enter a very exorbitant values which may cause this failure (such as ETC on one assignment 100,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000.00)

Data Warehouse would consider this as an invalid number and fail with error such as:

ClarityDB - isOracle? - An error occurred executing this job entry :
Couldn't execute SQL: BEGIN
DWH_INV_ASSIGN_SUM_FACTS_LOAD
(
P_DBLINK => 'CLARITYNAM21LINK',
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


Caused by:
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.

 

Raised as:

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
In progress


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 dwh_inv_assign_sum_facts_v@DBLINK
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

Outcomes