DWH Sync fails with ORA-01422: exact fetch returns more than requested number of rows

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

DWH Sync is failing and error in bg-dwh logs is:

ORA-20100: Encountered exception while executing CLARITY_DWH_SYNC_SP procedure. ORA-20100: Encountered exception while inserting row in table DWH_TME_ENTRY_LN. SQLERRM: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "PPM_DWH.CLARITY_DWH_SYNC_SP", line 216
ORA-06512: at line 2

This can happen on other tables, such as DWH_INV_INVESTMENT, :

2025/11/15 12:43:53 - Sync Tables(O) - ORA-20100: Encountered exception while executing CLARITY_DWH_SYNC_SP procedure. ORA-20100: Encountered exception while inserting row in table DWH_INV_INVESTMENT. SQLERRM: ORA-01422: exact fetch returns more than requested number of rows
2025/11/15 12:43:53 - Sync Tables(O) - ORA-06512: at "PPM_DWH.CLARITY_DWH_SYNC_SP", line 216

You can also see this message on parent keys not being there:

ORA-20100: Encountered exception while executing CLARITY_DWH_SYNC_SP procedure. ORA-20100: Encountered exception while inserting row in table DWH_TME_ENTRY_LN. SQLERRM: ORA-02291: integrity constraint (PPM_DWH.DWH_TME_ENTRY_LN_FK1) violated - parent key not found
ORA-06512: at "PPM_DWH.CLARITY_DWH_SYNC_SP", line 216
ORA-06512: at line 2

Environment

Clarity 16.3.3 +

Cause

Load DWH job failure prior to DWH Sync run causes parent keys to be missing

Resolution

Check to confirm Load DWH job had failed with a different error before this issue started happening

As Load DWH fails, some of the parent keys are not inserted and that will cause DWH Sync to fail as well

To Resolve this:

1. Disable DWH Sync in System Settings
2. Run Load DWH Full to completion
3. Then enable DWH Sync again

Ensure you set yourself to be notified if DWH job fails to address this going forward