Oracle maximum number of expression issue

Question

I have this query

@Query("SELECT b from BillInfoDetails b where b.masterAcctCode in :masterAccountList and b.msisdn in :msisdnList") List<BillInfoDetails>findAllByMsisdnAndMasterAcctList(@Param("masterAccountList")List<String> masterAccountList, @Param("msisdnList") List<String> msisdnList);

and then find this error ORA-01795: maximum number of expressions in a list is 1000. This error has some manual solution that I have split the list manually 1 to 999 and then 1000 to 1999 and so on. But this will not the good one for me cause in this msisdnList there could be 1500 or 18000 or some other more values. Moreover I want a dynamic solution actually where any dynamic value whatever it is, it will work properly

Does this answer your question? [Is there a workaround for ORA-01795: maximum number of expressions in a list is 1000 error?](https://stackoverflow.com/questions/17842453/is-there-a-workaround-for-ora-01795-maximum-number-of-expressions-in-a-list-is) or [SQL IN Clause 1000 item limit](https://stackoverflow.com/questions/4722220/sql-in-clause-1000-item-limit) — astentx, Jul 18 '22 at 07:14

score 0 · Answer 1 · answered Jul 18 '22 at 06:03

One option is to store all those values into a table; then you'd be able to use it as a join (or a subquery). For example:

select b.*
from billinfodetails b join new_table n on n.masteracctcode = b.masteracctcode

or

select b.*
from billinfodetails b
where b.masteracctcode in (select n.masteracctcode
                           from new_table n)

or

select b.*
from billinfodetails b
where exists (select null
              from new_table n
              where n.masteracctcode = b.masteracctcode)

Oracle maximum number of expression issue

1 Answers1