Ofbiz generates queries with large IN clauses

Ofbiz generates queries with large IN clauses.

Currently, Oracle will not accept a query which contains an IN clause with more than 1000 elements.

You will get an exception in the logs that looks like

com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following: [ query here ] (ORA-01795: maximum number of expressions in a list is 1000

We should change the oracle dialect so that this doesn't happen. There are two alternatives:

1. Batch IN clauses that contain more than 1000 elements. This is less risky but could cause performance issues (There's a reason to these limits in Oracle). A fix like this might be suitable for a point release, because we would only batch when we got to more than 1000 elements and only oracle databases, so the amount of customers affected by the change would be relatively small.

2. Generate sub-queries instead of IN clauses. This is technically more correct and will probably perform better. This is a more risky change, we could reduce the risk by generating sub-queries only when we have more than 1000 elements. This should probably be done as part of a full release to allow more time for testing and dogfooding.

Both fixes should only be done for Oracle database to limit the impact.