Incremental load job is failing with ORA-01439: column to be modified must be empty to change datatype

Products

• Clarity PPM On Premise

Issue/Introduction

We have an issue where the incremental load job is failing with the below error.

MOD_COL_SQL - org.pentaho.di.core.exception.KettleStepException: 
Error while running this step!

Couldn't execute SQL: ALTER TABLE DWH_ODF_XXX MODIFY COLUMN_NAME NUMBER(32)

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-01439: column to be modified must be empty to change datatype

Environment

Release : 15.9.1

Component :

Cause

This is caused when you don't remove the attributes from DWH before re-creating the attribute.

Resolution

1. Find out attribute which is causing this using COLUMN_NAME which is part of the logs. Run the below Query on DWH schema.
   
   SELECT object_code,attribute_code FROM dwh_meta_COLUMNS WHERE upper(DWH_COLUMN_NAME) ='COLUMNNAME';
   
   
2. Take backup of DWH_META_COLUMNS and DWH_META_TABLES on DWH schema and TRUNCATE them.
3. Navigate to Administration -> Objects -> Object(which is obtained from query) -> attribute and exclude from DWH.
4. Run the Load Datawarehouse full Load Job.
5. Once the job is complete, include the attribute back to DWH which was excluded in step 3 and re-run the Load Datawarehouse Full Load Job.

 

Note :- the Steps and error pertain to Oracle Database only.