search cancel

"Error 500 or ORA-01795: maximum number of expressions in a list is 1000 error." When we have more than 1000 records on the pull-down as Display type for lookup values

book

Article ID: 201806

calendar_today

Updated On:

Products

Clarity PPM SaaS

Issue/Introduction

When we have more than 1000 records on the pull-down as Display type for lookup values in any of the attribute located in any of the pages shows Error 500 in clarity PPM. 

app_ca.logs shows error such as "ORA-01795: maximum number of expressions in a list is 1000 error." 

 

Cause

On the pull-down as Display type for lookup values, PPM generates SQL statements as follows.

 SELECT … IN (xxxxx,yyyyy,zzzzz,.....)

When we have more than 1000 records on the pull-down as Display type for lookup values then we reach the Oracle limitation and have Error 

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

When an object instance exists in memory, there is no fixed limit on the number of attributes in the object. But the maximum total amount of memory consumed by an object instance is 4 GB.When an object instance is inserted into a table, the attributes are exploded into separate columns in the table, and the Oracle 1000-column limit applies.

Environment

Release : 15.X

Component : CA PPM SAAS APPLICATION

Resolution

The resolution is redesigning the lookup which has less than 1000 records or using the Browse as Display type for lookup values.

Additional Information

More information regarding oracle limits can found below:- 

https://docs.oracle.com/cd/B28359_01/server.111/b28320/limits003.htm#i288032