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

WANG XIAOBO

Description

BIP datamodel parameter cannot process LOV more than 999 values , it will run into error

 

 

 

Use Case and Business Need

My issue:

Create a BI Publisher Report , there is a sql script ' where segment3 in (:Parameter), Parameter Type :Menu, "Multiple Selection" option enabled, "Can select all" option enabled, select 'All Values Passed' option,
it will run into error:
oracle.xdo.servlet.data.DataException:
oracle.xdo.servlet.data.DataException:
oracle.xdo.servlet.data.DataException:
oracle.xdo.XDOException: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000

Oracle answer:

The LOV is limited to return a maximum of 999 row. This is due to a constraint by the database In Clause (which in turn cannot take more than 1000 distinct values)

But:

Even LOV maximum return is 999 row, I still can seach and move all LOV multiple times for one parameter by Seach/F12(refer attachment file'Seach and Move Multiple Times'), then there will be more than 999 values selected in 'Selected' list. then it will run into 'more than 1000' error. There must be other customer have the same problem, what's the best practice for such a requirement, the reality is there is more than 1000 values, how can we only allow 999 be selected, it will miss some data on this report, it unreasonable.

it is a database problem, is there any possible to make some change on database?

Original Idea Number: aefc61d6f5

Yashit Gulati-207563

Hi,

You have to put condition in your WHERE CLAUSE for mentioned ERROR(maximum number of expressions in a list is 1000):-

 -> WHERE (NVL (segment3, 'XYZ') IN (:Parameter_Name) OR 'ALL' IN (:Parameter_Name||'ALL') )

Thanks,
Yashit G.