Datamart Extraction job failing post upgrade

book

Article ID: 4355

calendar_today

Updated On:

Products

CLARITY PPM FOR ITG CLARITY PPM FEDERAL Clarity PPM SaaS - Application Clarity PPM On Premise

Issue/Introduction

Post upgrade to 14.3, the Datamart Extraction job intermittently fails with an exception in the bg-ca.log file:

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-06550: line 0, column 0:
PLS-00907: cannot load library unit CLARITY.NBI_EXTRACT_SP (referenced by )....

This problem is not seen in most environments.

Cause

The PPM Stored Procedures may not rebuild the index "NBI_PRTF_FM_U1" correctly every time: "CMN_DROP_INDEX_SP" and "CMN_CREATE_INDEX_SP".

This problem may be isolated to certain environments or data sets.

 

 

 

Environment

Oracle 11.2.0.3, 11.2.0.4PPM 14.3

Resolution

CA Support has not seen this in all environments.

The following workaround solution will resolve the issue.

1- Backup the following PPM schema Stored Procedure: "NBI_PROJECT_CURRENT_FACTS_SP".

2- Locate the following section: "V_STMT := 'Calling NBI_PRTF_FM_SP since FM is enabled';"

3- Modify this block:

Original:
V_STMT := 'Calling NBI_PRTF_FM_SP since FM is enabled';
EXECUTE IMMEDIATE 'TRUNCATE TABLE NBI_PRTF_FM';
SELECT NVL(tablespace_name,'') into v_u1_index_ts_name FROM user_indexes WHERE table_name = 'NBI_PRTF_FM' AND index_name='NBI_PRTF_FM_U1';
CMN_DROP_INDEX_SP('NBI_PRTF_FM_U1');
NBI_PRTF_FM_SP (P_CURRENCY_CODE);
CMN_CREATE_INDEX_SP('NBI_PRTF_FM','NBI_PRTF_FM_U1','UNIQUE','PROJECT_ID',v_u1_index_ts_name);

Please change this to:
V_STMT := 'Calling NBI_PRTF_FM_SP since FM is enabled';
EXECUTE IMMEDIATE 'TRUNCATE TABLE NBI_PRTF_FM';
SELECT NVL(tablespace_name,'') into v_u1_index_ts_name FROM user_indexes WHERE table_name = 'NBI_PRTF_FM' AND index_name='NBI_PRTF_FM_U1';
--CMN_DROP_INDEX_SP('NBI_PRTF_FM_U1');
NBI_PRTF_FM_SP (P_CURRENCY_CODE);
EXECUTE IMMEDIATE 'ALTER INDEX NBI_PRTF_FM_U1 REBUILD';

4- Rebuild the Stored Procedure, for example: "create or replace PROCEDURE NBI_PROJECT_CURRENT_FACTS_SP..."

5- Schedule the "Datamart Extraction" job and monitor to ensure the issue is resolved.

 

 

Additional Information

If there are any doubts, please work with CA Support to clarify.