Load DWH full error: "ORA-12899: value too large for column "PPM_DWH"."DWH_LKP_TRANSLATION"."TRANSLATION"..."

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

On a multilingual environment the Load Data Warehouse (DWH) full load job fails on Oracle with the error below: 

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-12899: value too large for column "PPM_DWH"."DWH_LKP_TRANSLATION"."TRANSLATION" (actual: 1500, maximum: 255)

(Note: Value for "actual" may vary.)

Environment

Database: Oracle

Cause

A translation with a value over 255 characters was entered on a lookup value, for a language included in the Data Warehouse

Resolution

1. On the PPM database, connect with a database query analyzer tool and run the query:
   
   select * from DWH_LOOKUPS_V where length(NAME )>255
   
   
2. In the results, note the name of the following:
   • Lookup (Lookup_type)
   • Lookup_code (the lookup_value)
   • the language
3. Go to Lookups and open the lookup type you found in the query results.
4. Go to the Values and find the value above
5. Click on the Translate icon
6. For the languages indicated, (all languages included in DWH), correct the Name to be under 255 characters.
   The Description field can go beyond this limit, so you don't have to change it.
7. Save and Return
8. Now run the Load Data Warehouse job - Full to completion

Additional Information

As a best practice, we recommend translation names for lookup values be short and concise and under 255 characters

See also KB: Data Warehouse (DWH) known issues