Load DWH fails after refresh ORA-06575: Package or function DWH_CAL_DATELABEL_FCT is in an invalid state

Document ID : KB000100335
Last Modified Date : 07/08/2018
Show Technical Document Details
Issue:
After refreshing an environment from Production, the Load Data Warehouse fails with error:
 
ORA-06575: Package or function DWH_CAL_DATELABEL_FCT is in an invalid state
 
 
Full Error:
[CA Clarity][Oracle JDBC Driver][Oracle]ORA-06575: Package or function DWH_CAL_DATELABEL_FCT is in an invalid state
ORA-06512: at "PPM_DWH.DWH_CFG_ADDIN_EXTRAS_SP", line 463
ORA-06512: at line 19
 
Upon debugging we can see the function is not compiled because this view has errors:
 
Error(30,49): PL/SQL: ORA-04063: view "PPM_DWH.DWH_RPT_LABELS_V" has errors
 
When opening the view we see this happens because it still points to the old DB link:
 
 
 CREATE OR REPLACE FORCE EDITIONABLE VIEW "PPM_DWH"."DWH_RPT_LABELS_V" ("LABEL_KEY", "LABEL_CODE", "LANGUAGE_CODE", "LABEL_NAME", "DESCRIPTION") AS
  SELECT L.ID LABEL_KEY, L.LABEL_CODE, NLS.LANGUAGE_CODE, NLS.NAME LABEL_NAME, NLS.DESCRIPTION FROM CMN_CAPTIONS_NLS@OLD_DB_LINK NLS, CMN_RPT_LABELS@OLD_DB_LINK L, CMN_LANGUAGES@OLD_DB_LINK LA WHERE NLS.TABLE_NAME = 'CMN_RPT_LABELS' AND NLS.PK_ID = L.ID AND NLS.LANGUAGE_CODE = LA.LANGUAGE_CODE AND LA.IS_DW_ENABLED = 1;
 
   COMMENT ON COLUMN "PPM_DWH"."DWH_RPT_LABELS_V"."LABEL_KEY" IS 'Primary key for the report label.  Internal key - links to cmn_rpt_labels.id (Only in PPM)';
   COMMENT ON COLUMN "PPM_DWH"."DWH_RPT_LABELS_V"."LABEL_CODE" IS 'Label code. (Only in PPM)';
   COMMENT ON COLUMN "PPM_DWH"."DWH_RPT_LABELS_V"."LANGUAGE_CODE" IS 'Language code';
   COMMENT ON COLUMN "PPM_DWH"."DWH_RPT_LABELS_V"."LABEL_NAME" IS 'Name of label';
   COMMENT ON COLUMN "PPM_DWH"."DWH_RPT_LABELS_V"."DESCRIPTION" IS 'Label description';
   COMMENT ON TABLE "PPM_DWH"."DWH_RPT_LABELS_V"  IS 'Contains action items assignees related to action items on risks';
  
Cause:
DE41772 After refresh, Load Data Warehouse job fails with error "ORA-06575: Package or function DWH_CAL_DATELABEL_FCT is in an invalid state" due to DB LINK hardcoded in view DWH_RPT_LABELS_V

Fixed in CA PPM 15.5
 
Resolution:
Workaround:
  1. Get the DDL for DWH_RPT_LABELS_V from your database
  2. Replace the @OLD_DB_LINK with @NEW_DB_LINK (exact database links names are required as per properties.xml
  3. Run the DDL to recreate the view
  4. Recompile the view DWH_RPT_LABELS_V
  5. Recompile the function DWH_CAL_DATELABEL_FCT
  6. Re-run the job