ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

Load DWH job Incremental fails with error "ORA-30926..." when there are duplicates in the lookup tables


Article ID: 103891


Updated On:


Clarity PPM SaaS Clarity PPM On Premise


When using a Parameterized lookup, and including the attribute in Data Warehouse (DWH), the Load Data Warehouse job - Full Load runs well, and then the Incremental fails with error:

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION IN DWH_LOOKUP_LOAD (DWH_LKP_TEST_00). SQLERRM : ORA-30926: unable to get a stable set of rows in the source tables
ORA-06512: at line 22


This error occurs where there are duplicates in the lookup tables. For parameterized lookups, it's usually caused by a wrong hidden key that does not use a unique key for a join.
To confirm this is the case do the following: 

  1. Go to Administration - Lookups
  2. Open the lookup - Query - note the table you're selecting from.
  3. Go to Administration - Lookups
  4. Open the lookup - Parent Window - Hidden key
  5. Check the hidden key column value in the table from step 1.

You see that the field selected is not having unique entries in the database table, allowing for duplicates. This is not recommended and Data Warehouse will not work with duplicates as it has enforced constraints to ensure data quality.

This was reviewed as DE39925 and determined to not be a bug


Release: All Supported
Component: Clarity Data Warehouse (DWH)


  1. The best practice when creating parameterized lookups is to always pick a hidden key that is a unique value.  In most cases, we select the ID in the custom object table. So for this case, it would be ODF_CA_TEST.ID.
    This would ensure there no duplicates will occur when joining the two tables and the Load Data Warehouse will always run well.
  2. If the above is not possible, uncheck the fields from the DWH until you can correct the lookups.
  3. The last possibility is to create two calculated fields (strings) referring to the same values:
    1. Uncheck the lookups from DWH
    2. Create two Calculated fields (strings) referring to the same values
    3. Include those in the DWH instead

Additional Information

See also: Data Warehouse (DWH) known issues