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

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

  

Hi,

     I have a two environment one for development and one for QA, we are suing the same version of oracle, and outsystem in both the environment, we are executing a sub query like  where Id in( id1,id2,1d3,.........) . 

In QA environment  i am getting the oracle error  'Error ORA-01795: maximum number of expressions in a list is 1000; but in Development environment its working fine. we have the same data in Development and QA environment.

Do you have any idea to fix this error ? 

Is there any property of oracle to increase the expression in the list?



Thanks

Rajendra Singh

Hi Rajendra,

In Oracle number of items in a "IN" operator cannot exceed 1000. I suppose use are using advanced sql statement with a parameter in the "IN" clause with the property "Expand Inline" = "True". In this case, the number of items in the "IN"operator is dependent on the data in the database. Although you state that the data is the same in both Dev and QA I would check the value of the parameter in both environments by debugging. There probably is a difference. I think there should be a better alternative for the statement with > 1000 items in an "IN" clause. 

Regards,


Matthieu

Rajendra koranga wrote:

Is there any property of oracle to increase the expression in the list?

Hi Rajendra,

There's no property you can change.

What you can do is insert your values in a temporary table and do a subquery:


Select {Table_A}.*

From {Table_A}

Where {Table_A}.[Id] In (

    Select {My_ID_Table}.[Id]  /* No limitations */

    From {My_ID_Table}

    Where <some condition only valid for this instant> 

)

Cheers,

João Heleno