PSR Numbers Are Missing In PQM_PR_ITEM_F Table In OPLA Database, Task Procedure-odi_pro_BI_PR_AFF_ITEM_pro-odi_pro_BI_PR_AFF_ITEM_cmd Failing with ORA-01422: exact fetch returns more than requested number of rows (Doc ID 2982172.1)

Last updated on FEBRUARY 15, 2024

Applies to:

Symptoms

Some Problem Service Request (PSR) numbers are missing in PQM_PR_ITEM_F table in Oracle Product Lifecycle Analytics (OPLA) database.
Same PSRs are there in the header table PQM_PR_F table and has data.

 

When checking Oracle Data Integrator (ODI), can see ODI_PKG_MDS_PQM_PR is failing. By expanding this, see BI_OR_AFF_ITEM_pro is throwing error.

ODI-1228: Task Procedure-odi_pro_BI_PR_AFF_ITEM_pro-odi_pro_BI_PR_AFF_ITEM_cmd fails on the target connection TRG_BI_PHYSICAL.
Caused By: java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "MDS.MDS_PR_AFFECTED_ITEM_PRO", line 482
ORA-06512: at "MDS.MDS_PR_AFFECTED_ITEM_PRO", line 468
ORA-06512: at "MDS.MDS_PR_AFFECTED_ITEM_PRO", line 468
ORA-06512: at "MDS.MDS_PR_AFFECTED_ITEM_PRO", line 451
ORA-06512: at "MDS.MDS_PR_AFFECTED_ITEM_PRO", line 468


The issue can be reproduced at will with the following steps:

1. Log into Web Client, and search and open a PSR.
2. Go to Affected Items tab, and see the Item is there.
3. Login into OPLA.
4. Run the CDC job.
5. Login to Multi-Dimensional Schema (MDS) schema, and check header table PQM_PR_F and PQM_PR_ITEM_F item table data.
6. Unable to find the PSR and its item in PQM_PR_ITEM_F table.
7. Go to ODI Operator, and from Session List, expand Status. See the error with ODI_PKG_MDS_PQM_PR.

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.