Load DWH Job fails with ORA-00904: "LANGUAGE_ID": invalid identifier

book

Article ID: 195902

calendar_today

Updated On:

Products

Clarity PPM SaaS Clarity PPM On Premise

Issue/Introduction

The Load Data Warehouse is failing with the below found in the bg-dwh.logs:

ERROR 2020-07-24 06:34:22,714 [Thread-337028] dwh.event ClarityDB - isOracle? - An error occurred executing this job entry :
Couldn't execute SQL: DECLARE
    V_SQL_TEXT CLOB;
    V_SQLCODE NUMBER;
    V_SQLERRM VARCHAR2(4000);
.........................

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-06550: line 6, column 275:
PL/SQL: ORA-00904: "LANGUAGE_ID": invalid identifier
ORA-06550: line 6, column 1:
PL/SQL: SQL Statement ignored


ERROR 2020-07-24 06:34:22,726 [dwh_etl_interface_lkp - User Defined Java Class] dwh.event User Defined Java Class - Unexpected error
ERROR 2020-07-24 06:34:22,729 [dwh_etl_interface_lkp - User Defined Java Class] dwh.event User Defined Java Class - java.lang.RuntimeException: ERROR: THERE WERE ERRORS DURING LOOKUP JOB EXECUTION FOR THE TABLE - <Custom lookup name>
 at Processor.processRow(Processor.java:64)
at org.pentaho.di.trans.steps.userdefinedjavaclass.UserDefinedJavaClass.processRow(UserDefinedJavaClass.java:1181)

Cause

The error ORA-00904: "LANGUAGE_ID": invalid identifier points to an issue with the Language_ID with the referenced lookup, due to it either being missing or in a case (lower case) that the system may not be accepting

Environment

Release : All Supported Releases

Component : CA PPM SAAS DATA WAREHOUSE

Resolution

  1. Check to see if the LANGUAGE_ID field is in the query associated with the lookup.
    1. If not, add it to the query.
    2. If it is there, but in lower case, try changing it to capitalized which helped in the following issue: Load DataWarehouse' Job Failing

Tip: To identify the attribute the lookup is associated with to exclude it from the DWH until you are able to fix the root cause, see: 110060

 

 

Additional Information

See KB: 141061 for tips on how to optimize use of Self Service for Clarity PPM