Clarity PPM1

Expand all | Collapse all

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

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

    Posted 12-04-2017 10:23 AM

    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



  • 2.  Re: Tech Tip: Load Data Warehouse job fails with ORA-12899 on DWH_LKP_TRANSLATION

    Posted 12-08-2017 02:47 PM

    Thank you for sharing this tip with the community Nika!

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