CODEWITHSUNDEEP
javaarraysangularjsjakarta-eeoracle-sqldeveloper
user3902443
user3902443

Reputation: 39

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

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;

Upvotes: 2

Views: 7707

Answers (1)

Braj
Braj

Reputation: 46841

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

  1. Use inner query to solve this issue. You can create a temporary table and use that in your IN clause.

  2. 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)
  ...

Read more.. and see Oracle FAQ

Upvotes: 5

Related Questions