java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000

Question

we all know we see this exception when try to bomabard the query with more than 1000 values. the maximum for the column limit is 1000.. the best possible solution is to split the query into two.guys can u suggest some possible ways of code refactoring to make my problem go away.any help would be appreciated.

we see the exception when audTypeFieldIdList are greater than 1000 values.

try {
        String query = "select AUDIT_TYPE_FIELD_ID, FIELD_NAME from AUDIT_TYPE_FIELD where AUDIT_TYPE_FIELD_ID in (";
        int x = 0;
        for (int y = 1; y <= audTypeFieldIdList.size(); y++) {
            query += audTypeFieldIdList.get(x);
            if (y != audTypeFieldIdList.size()) {
                query += ", ";
            }
            x++;
        }
        query += ")";    



List<Long> audTypeFieldIdList, Connection connection) {          

    ResultSet rs = null;
    Statement stmt = null;
    List<AuditTypeField> audTypeFieldList = new ArrayList<AuditTypeField>();
    try {
        String query = "select AUDIT_TYPE_FIELD_ID, FIELD_NAME from AUDIT_TYPE_FIELD where AUDIT_TYPE_FIELD_ID in (";
        int x = 0;
        for (int y = 1; y <= audTypeFieldIdList.size(); y++) {
            query += audTypeFieldIdList.get(x);
            if (y != audTypeFieldIdList.size()) {
                query += ", ";
            }
            x++;
        }
        query += ")";



        stmt = connection.createStatement();
        rs = stmt.executeQuery(query);
        while (rs != null && rs.next()) {
            AuditTypeField audTypeField = PluginSystem.INSTANCE
                    .getPluginInjector().getInstance(AuditTypeField.class);
            audTypeField.setId(rs.getLong("AUDIT_TYPE_FIELD_ID"));
            audTypeField.setName(rs.getString("FIELD_NAME"));
            audTypeFieldList.add(audTypeField);
        }
        return audTypeFieldList;
        return audTypeFieldList;

Where are the 1000+ elements in the list coming from? Presumably, a human isn't entering that many values. Are they coming from a different query against the database? If so, you'd want to combine the two queries. — Justin Cave, Aug 02 '14 at 22:18
YES IT IS COMING FROM TWO DIFFERENT QUERYS AGAINST THE DATABASE — user3902443, Aug 03 '14 at 00:04
Please don't shout. So why are you doing two separate queries? — Alex Poole, Aug 03 '14 at 08:42

score 5 · Answer 1 · edited May 23 '17 at 12:16

5

You can't use more than 1000 entries in IN clause. There are 2 solutions as mentioned below:

Use inner query to solve this issue. You can create a temporary table and use that in your IN clause.
Break it in the batch of 1000 entries using multiple IN clause separated by OR clause.

sample query:

select * from table_name
  where
      column_name in (V1,V2,V3,...V1000)
  or
      column_name in (V1001,V1002,V1003,...V2000)
  ...

java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000

1 Answers1