Load DWH Full load fails with error ORA-06502

Products

• Clarity PPM On Premise

Issue/Introduction

While trying to run the load data warehouse job with full load, it fails with below error in bg-dwh logs

Pre_execution_config_step_orcl - An error occurred executing this job entry : 

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "DWH_SCHEMA_NAME.DWH_CFG_PRE_CONFIG_SP", line 40
ORA-06512: at line 4

Environment

Component: Clarity Data Warehouse

DWH Version: 16.0.2

Clarity Version:  16.0.2

Database: Oracle

Cause

During investigation it was observed that the above issue is occurring due to some of the index name length is more than the current data type limit set in the DWH_CFG_PRE_CONFIG_SP i.e. varchar(30). Please refer some useful queries below

To know the last successful runs of Load DWH

SELECT * FROM cmn_sch_jobs WHERE JOB_DEFINITION_ID =(SELECT id FROM CMN_SCH_JOB_DEFINITIONS csjd WHERE JOB_CODE ='DWH_ETL_MASTER')

 

Resolution

Workaround

1. Create a backup of store procedure  DWH_CFG_PRE_CONFIG_SP
2. Edit the store procedure DWH_CFG_PRE_CONFIG_SP and change the datatype V_INDEX_NAME from 30 to 60
   

   From

   V_INDEX_NAME VARCHAR(30);

   To

   V_INDEX_NAME VARCHAR(60);

    

3. Save the store procedure and run the Load DWH with full load

The issue is already fixed in 16.2.3 where the size of the V_INDEX_NAME is changed to 60