Datamart Extraction job fails with message 'no data found' after upgrade to Clarity 13.2 or applying patch 13.1.0.5 for 13.1

Document ID : KB000019230
Last Modified Date : 14/02/2018
Show Technical Document Details

Issue: 

The Datamart Extraction job failed after an upgrade to Clarity 13.2 with an Oracle Database implementation showing a message similar to this in the background log file.

Error  
PMD application error java.sql.SQLException: [CA Clarity][Oracle JDBC Driver][Oracle]
ORA-20000: Error in NBI_EXTRACT_SP - Calling NBI_EXTR_PRTF_SP:
ORA-20000: Error in NBI_EXTR_PRTF_SP - CHECKING IF THERE ARE ANY EVENTS TO PROCESS:
ORA-20000: Error in NBI_PRTF_SP - :
ORA-01403: no data found
ORA-06512: at "CLARITYPROD.NBI_EXTRACT_SP", line 375
ORA-06512: at line 1

Steps to Reproduce:

  1. Install CA Clarity PPM 13.2.0
  2. Apply the current Generic Patch
  3. Do not grant the ALTER session right
  4. Execute the 'Datamart Extraction' job and it fails
  5. Grant the ALTER session right and execute the job again

Cause: 

Caused by CLRT-72162

Resolution: 

 

Resolved in Clarity 13.2 Generic Patch  
Resolved in Clarity 13.3  

Workaround: 

GRANT ALTER SESSION TO CLARITY; is a required privilege and is documented in 13.2 installation guide and 13.1.x patch 'Readme' file.

If the 'Datamart Extraction' job is executed without the ALTER session privilege, some indexes dropped during the execution of the job will not exist due to the job failure.

Please create the missing indexes as below, changing the INDX_LARGE tablespace as appropriate.

The following queries can be used to identify all the indexes.

SELECT index_name FROM user_indexes WHERE table_name = 'NBI_PRT_FACTS' AND index_name IN ('NBI_PRT_FACTS_N1','NBI_PRT_FACTS_N2','NBI_PRT_FACTS_N3','NBI_PRT_FACTS_N4','NBI_PRT_FACTS_U2',);

Index Name

NBI_PRT_FACTS_N1
NBI_PRT_FACTS_N2
NBI_PRT_FACTS_N3
NBI_PRT_FACTS_N4
NBI_PRT_FACTS_U2

If any of the indexes are missing, then execute the SQL statement(s) below to recreate the missing indexes.

NOTE: The Tablespace Name could be different in your specific implementation.  Make a change if needed.  

EXEC CMN_CREATE_INDEX_SP('NBI_PRT_FACTS','NBI_PRT_FACTS_N1','','PROJECT_ID, FACT_DATE','INDX_LARGE');
EXEC CMN_CREATE_INDEX_SP('NBI_PRT_FACTS','NBI_PRT_FACTS_N2','','RESOURCE_ID, FACT_DATE','INDX_LARGE');
EXEC CMN_CREATE_INDEX_SP('NBI_PRT_FACTS','NBI_PRT_FACTS_N3','','PRJ_OBJECT_ID, FACT_DATE','INDX_LARGE');
EXEC CMN_CREATE_INDEX_SP('NBI_PRT_FACTS','NBI_PRT_FACTS_N4','','PROJECT_ID, RESOURCE_ID, FACT_DATE, TASK_ID','INDX_LARGE');
EXEC CMN_CREATE_INDEX_SP('NBI_PRT_FACTS','NBI_PRT_FACTS_U2','UNIQUE','PROJECT_ID, TEAM_ID, FACT_DATE, TASK_ID','INDX_LARGE');

  

The following query will check if NBI_R_FACTS index exists or is missing:

SELECT index_name FROM user_indexes WHERE table_name = 'NBI_R_FACTS' AND index_name IN ('NBI_R_FACTS_U1');

If the index NBI_R_FACTS is missing, be sure to also execute the following statement:

EXEC CMN_CREATE_INDEX_SP('NBI_R_FACTS','NBI_R_FACTS_U1','UNIQUE','FACT_DATE, RESOURCE_ID',<tablespace name>);

 

The following query will check if NBI_PRTF_FM index exists or is missing:

SELECT index_name FROM user_indexes WHERE table_name = 'NBI_PRTF_FM' AND index_name IN ('NBI_PRTF_FM_U1');

If the index NBI_PRTF_FM is missing, be sure to also execute the following statement:

EXEC CMN_CREATE_INDEX_SP('NBI_PRTF_FM','NBI_PRTF_FM_U1','UNIQUE','PROJECT_ID',<tablespace name>); 

 

Additional Information: 

Reference TEC599354 : PPM 13.2 Generic Patch available to resolve reported issues

For a related issue, reference TEC593413 : ORA-04068: existing state of packages has been discarded - Datamart Job in Oracle RAC