Announcement Announcement Module
No announcement yet.
ORA-01795: maximum number of expressions in a list Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • ORA-01795: maximum number of expressions in a list

    Hi Guys,
    We are having issue with query exceeding maximum number of expressions > 1000. we are using ibatis. It looks the issue with oracle that wont allow list more than 1000 in in parameter.

    i am looking for work around that in my sql i need to pass whole set of values at for sure i cant go for multiple calls.
    second thing is there anyway in ibatis query that i can handle this issue?

    any suggestions would be appreciated

    		select to_char(ps_vz_mettrftotal.VZ_ITM_UID) as CATEGORYID,
    		PS_VZ_METTRFTOTAL, ps_vz_geo_xref where
    		ps_vz_mettrftotal.VZ_PPC_GEO_ID = ps_vz_geo_xref.VZ_PPC_GEO_ID
    		and ps_vz_mettrftotal.VZ_PPC_GEO_ID in
    		<iterate property="geos" open="(" close=")" conjunction=",">
    		group by ps_vz_mettrftotal.VZ_ITM_UID

  • #2
    This is really more of an iBatis/SQL problem than a spring problem, perhaps this is the wrong forum to post this problem to.

    I'm not familiar with iBatis, but what you've hit is a hard limit to the number of parameters that Oracle allows in the IN(...) clause. You'll need to restructure your query to not use an IN(...) clause.

    One possibility would be to identify something that is common to those 1000+ geo_id values and base a query on that. Perhaps a separate table with the geo_ids and a flag column against them, then use the flag in a JOIN query joining the table you want to query with this other table based on the value of the flag. Hope that makes sense.


    • #3
      It is easy enough to side step this problem by keeping a count of the number of items in the list. When this count hits 1000 close the list and add "OR GEO_ID in (" reset your counter and continue. You can have as many lists as needed as long as none of them exceed 1000 items.