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.1Workaround:
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
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.
7. Run Load Data Warehouse - Full