Datamart Extraction job fails with error in NBI_EXTR_PCF_SP

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

The job fails with the BG log error:

java.sql.SQLException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-20000: Error in NBI_EXTRACT_SP - Executing NBI_EXTR_PCF_SP: ORA-20000: Error in NBI_EXTR_PCF_SP - executing NBI_PROJECT_CURRENT_FACTS_SP: ORA-20000: Error in NBI_PROJECT_CURRENT_FACTS_SP - Calling NBI_PRTF_FM_SP since FM is enabled: ORA-01403: no data found

ORA-06512: at "PPM1510.NBI_EXTRACT_SP", line 362

ORA-06512: at line 1 

Environment

Release: 
Component:

Cause

• This issue is caused by an interruption in the Datamart Extraction job, specifically in the stored procedure NBI_PROJECT_CURRENT_FACTS_SP which calls stored procedure NBI_PRTF_FM_SP.
• Within the NBI_PRTF_FM_SP, the index NBI_PRTF_FM_U1 is dropped but if the interruption occurs after it is dropped, it does not recover, therefore the index will need to be manually recreated.

Resolution

1.Log in as the database user.

2.Run:

select * from user_indexes where table_name = 'NBI_PRTF_FM' 

If the index(NBI_PRTF_FM_U1) does not exist, run command:

execute CMN_CREATE_INDEX_SP('NBI_PRTF_FM','NBI_PRTF_FM_U1','UNIQUE','PROJECT_ID','the_index_tablespace');

3. Rerun the Datamart Extraction job.

Additional Information

Also check:
Clarity Tech Tip: How to check the Datamart (Datamart Extraction and Datamart Rollup jobs)