Load DWH Job fails if the column TABLE_NAME is null for table DWH_CMN_ERROR_MESSAGE
search cancel

Load DWH Job fails if the column TABLE_NAME is null for table DWH_CMN_ERROR_MESSAGE

book

Article ID: 207342

calendar_today

Updated On:

Products

Clarity PPM On Premise Clarity PPM SaaS

Issue/Introduction

Load Data Warehouse (DWH) Job fails with error: ORA-20100: ENCOUNTERED EXCEPTION WHILE INSERTING INTO HISTORY TABLES. SQLERRM : ORA-01400: cannot insert NULL into ("PPM_DWH"."DWH_CMN_ERROR_MSG_HISTORY"."TABLE_NAME")
 ORA-06512: at "PPM_DWH.DWH_CFG_POP_HISTORY_SP", line 81

Steps to Reproduce:

  1. Run the Load Data Warehouse and ensure it's successful
  2. In Order to reproduce the Load DWH failing, run the below update (Note: The column name TABLE_NAME  can get null due to some other factors too but that is yet to be found) 

 UPDATE DWH_CMN_ERROR_MESSAGE 
 SET TABLE_NAME = NULL 

    3. Re-Run the Load Data Warehouse Job

Expected Results: The Load Dataware house Job should be successful as column TABLE_NAME can accept NULL 

Actual Results: The Load Dataware house Job fails with the below error:

 [CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION WHILE INSERTING INTO HISTORY TABLES. SQLERRM : ORA-01400: cannot insert NULL into ("PPM_DWH"."DWH_CMN_ERROR_MSG_HISTORY"."TABLE_NAME")
  ORA-06512: at "PPM_DWH.DWH_CFG_POP_HISTORY_SP", line 81
  ORA-06512: at line 2

Environment

Release : 15.9, 15.9.1

Component : CLARITY DATA WAREHOUSE

Cause

Defect DE59585

Resolution

Fixed in 15.9.2

Workaround 

For On Premise Customers: 

  1. Pause any scheduled jobs and ensure any running jobs to be completed 
  2. Stop the BG services 
  3. Back up the table DWH_CMN_ERROR_MESSAGE
  4. Delete any record from table DWH_CMN_ERROR_MESSAGE  where TABLE_NAME IS NULL ( Below is the sample from one of the problematic environments) 
TABLE_NAME ERROR_MESSAGE ERROR_NUMBER SQL_COMMAND
HISTORY TABLES ORA-01400: cannot insert NULL into ("PPM_DWH"."DWH_CMN_ERROR_MSG_HISTORY"."TABLE_NAME") -1400 INSERT INTO DWH_CMN_ERROR_MSG_HISTORY (LOAD_DWH_KEY, TABLE_NAME, ERROR_MESSAGE, ERROR_NUMBER, SQL_COMMAND, HIST_POPULATE_DATE) (SELECT 5015092, TABLE_NAME, ERROR_MESSAGE, ERROR_NUMBER, SQL_COMMAND, TO_DATE('27-jan-2021 04:20:12','dd-mon-yyyy hh24:mi:ss') FROM DWH_CMN_ERROR_MESSAGE)
HISTORY TABLES ORA-01400: cannot insert NULL into ("PPM_DWH"."DWH_CMN_ERROR_MSG_HISTORY"."TABLE_NAME") -1400 INSERT INTO DWH_CMN_ERROR_MSG_HISTORY (LOAD_DWH_KEY, TABLE_NAME, ERROR_MESSAGE, ERROR_NUMBER, SQL_COMMAND, HIST_POPULATE_DATE) (SELECT 5015096, TABLE_NAME, ERROR_MESSAGE, ERROR_NUMBER, SQL_COMMAND, TO_DATE('27-jan-2021 05:09:12','dd-mon-yyyy hh24:mi:ss') FROM DWH_CMN_ERROR_MESSAGE)
HISTORY TABLES ORA-01400: cannot insert NULL into ("PPM_DWH"."DWH_CMN_ERROR_MSG_HISTORY"."TABLE_NAME") -1400 INSERT INTO DWH_CMN_ERROR_MSG_HISTORY (LOAD_DWH_KEY, TABLE_NAME, ERROR_MESSAGE, ERROR_NUMBER, SQL_COMMAND, HIST_POPULATE_DATE) (SELECT 5015094, TABLE_NAME, ERROR_MESSAGE, ERROR_NUMBER, SQL_COMMAND, TO_DATE('27-jan-2021 04:37:31','dd-mon-yyyy hh24:mi:ss') FROM DWH_CMN_ERROR_MESSAGE)
  ORA-01403: no data found 100  
HISTORY TABLES ORA-01400: cannot insert NULL into ("PPM_DWH"."DWH_CMN_ERROR_MSG_HISTORY"."TABLE_NAME") -1400 INSERT INTO DWH_CMN_ERROR_MSG_HISTORY (LOAD_DWH_KEY, TABLE_NAME, ERROR_MESSAGE, ERROR_NUMBER, SQL_COMMAND, HIST_POPULATE_DATE) (SELECT 5015093, TABLE_NAME, ERROR_MESSAGE, ERROR_NUMBER, SQL_COMMAND, TO_DATE('27-jan-2021 04:27:51','dd-mon-yyyy hh24:mi:ss') FROM DWH_CMN_ERROR_MESSAGE)
HISTORY TABLES ORA-01400: cannot insert NULL into ("PPM_DWH"."DWH_CMN_ERROR_MSG_HISTORY"."TABLE_NAME") -1400 INSERT INTO DWH_CMN_ERROR_MSG_HISTORY (LOAD_DWH_KEY, TABLE_NAME, ERROR_MESSAGE, ERROR_NUMBER, SQL_COMMAND, HIST_POPULATE_DATE) (SELECT 5015095, TABLE_NAME, ERROR_MESSAGE, ERROR_NUMBER, SQL_COMMAND, TO_DATE('27-jan-2021 04:56:02','dd-mon-yyyy hh24:mi:ss') FROM DWH_CMN_ERROR_MESSAGE)

 

Additional Information

Reference also:

Powered by Wolken Software