Nika_Hadzhikidi

Tech Tip: Load Data Warehouse job fails with ORA-12899 on DWH_LKP_TRANSLATION

Discussion created by Nika_Hadzhikidi Employee on Dec 4, 2017
Latest reply on Dec 8, 2017 by Chris_Hackett

Hello everyone,

 

I thought I'd share an issue I had with a customer:

 

Summary:

On a multilingual environment:

Load Data Warehouse job - Full Load on Oracle fails with error:

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

 

(Value for "actual" may vary.)

 

Root Cause:

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

 

Solution:

  1. On the PPM database, please connect with a database query analyser tool and run the query:

select * from DWH_LOOKUPS_V where length(NAME )>255

2.In the results, note the name of the Lookup (Lookup_type) and Lookup_code (the lookup_value), and the language.

  1. Now go to Lookups and open the lookup type you found in the query results.
  2. Go to the Values and find the value above, click on the Translate icon
  3. For the languages indicated, (so all languages included in DWH), correct the Name to be under 255 characters. The Description field can go beyond this limit, so you may leave it and not change it.
  4. Save and Return
  5. Now run Load Data Warehouse Full until completion

 

 

As best practice we recommend you to keep the translation names for lookup values to be short and concise and under 255 characters

 

 

I've posted this as:

KB TEC1814223 which should be available shortly.

 

Hope this helps -Nika

Outcomes