JPQL IN clause limit

[Marcus Noble]

Is it possible to get around the limitation on the number of options passed into an IN clause in a JPQL query?

For example:

SELECT obj FROM Object obj WHERE obj.id IN (1 ... 2000)

I have tried to split it up into multiple IN clauses, limiting each to 900 options but ICAT always throws the following error:

ICATInternalError: Unexpected DB response class javax.persistence.PersistenceException Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column


Error Code: 1461
Call: INSERT INTO LOG (ID, CREATE_ID, CREATE_TIME, DURATION, ENTITYID, ENTITYNAME, MOD_ID, MOD_TIME, OPERATION, QUERY) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        bind => [10 parameters bound]
Query: InsertObjectQuery(org.icatproject.core.entity.Log@17ed8797)

Is it possible to do this in a single query or must I resort to breaking it down into multiple queries?

Thanks,

Marcus

[tom.g...@stfc.ac.uk]

Hi Marcus,

 

What is the context for doing this? – where are you getting the list of ids from?

 

I wonder if the query could be done in a different way?

 

Best,

Tom

--
You received this message because you are subscribed to the Google Groups "icatgroup" group.
To unsubscribe from this group and stop receiving emails from it, send an email to icatgroup+...@googlegroups.com.
To post to this group, send email to icat...@googlegroups.com.
Visit this group at http://groups.google.com/group/icatgroup.
For more options, visit https://groups.google.com/d/optout.

[Marcus Noble]

I'm looking to get back all Investigations based on all RB numbers I have in a database.

This is currently not many but will increase over time.

Something like...

SELECT i FROM Investigation i JOIN i.investigationInstruments inst WHERE i.name IN (...) AND inst.instrument.name IN (...) INCLUDE i.investigationInstruments.instrument

But, all I really want back is a list of RB number/instrument name pairs.

Marcus

[Steve Fisher]

The problem is that the Log table has a type of VARCHAR2(4000 BYTE) in Oracle. I will change the code to prune queries down to 4000 as they are processed for logging. Meanwhile please change your code so that you check that the query string does not exceed 4000 characters.  See new bug https://code.google.com/p/icatproject/issues/detail?id=145. 

However, after making the fix to issue 145 If you generate a query with a long IN clause it will still fail if you exceed the Oracle limit of 1000 "? parameters". It would be difficult for me to trap this condition and split the query especially in the case with multiple IN expressions so I propose to leave this as a limitation of the implementation.

On Thursday, 2 October 2014 11:42:10 UTC+1, Marcus Noble wrote:

[Marcus Noble]

I have since changed my query (split it into multiple) to remove the need for so many IN parameters.

In any case, the 1000 limit imposed by Oracle isn't an issue and can be split into multiple IN clauses in a single query. This would rely on the bug you have opened being resolved though.