ORA-01795: maximum number of expressions in a list is 1000 - Clarity PPM lookups

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Following error message is obtained in the app-ca.log in one of these scenarios:

1. More than 1000 records are fetched by a lookup field. It is configured to display as a drop down
2. More than 1000 records are selected and saved in a lookup type field

ORA-01795: maximum number of expressions in a list is 1000

Error 500 appears in the UI.

Cause

In both these scenarios, the SQL that the application generates will contain an IN clause in which all the records fetched or saved in the lookup field are passed as input. Oracle has a limitation of not passing more than 1000 records in an IN clause.

Resolution

1. If the lookup is fetching more than 1000 values, change the display type of the field from drop-down to browse
2. If the lookup field has more than 1000 values saved, remove some values from it. Prevent these many values getting saved by redesigning the functionality, if it is a customization.

Additional Information

Oracle limitation of 1000 values per IN clause:

• https://support.oracle.com/knowledge/More%20Applications%20and%20Technologies/2228062_1.html