ORA-01795: 列表中的最大表达式数为 1000

今天查看日志的时候发现多次出现如下的异常，查阅了资料后发现IN语句中写的表达式的最大数量不能超过1000。

ORA-01795: 列表中的最大表达式数为 1000

1. 00000 - "maximum number of expressions in a list is 1000"
   Cause: Number of expressions in the query exceeded than 1000.
   Note that unused column/expressions are also counted Maximum number of expressions that are allowed are 1000.
   Action: Reduce the number of expressions in the list and resubmit.
   行 22 列 2,586 出错

查看代码后发现有一个意思大概如下的代码。

public class InTest {
    @Test
    public void test() throws SQLException {
        OracleDBUtil util = new OracleDBUtil();
        Connection conn = util.connection();
        Statement st = conn.createStatement();
        String instr = "";
        for (int i = 0; i < 2000; i++) {
            instr += "," + i;
        }
        if (instr.length() > 1) {
            instr = instr.substring(1);
        }
        if (instr.length() > 0) {
            String sql = "select userid from LOG where id in(" + instr + ")";
            System.out.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next()) {
                System.out.println(rs.getString("userid"));
            }
        }
        util.close(conn);
    }
}

解决方法就是拆分IN里面的条件，将表达式的数量控制在1000以内，然后通过OR语句连接。

(field in(s1,s2,s3.....s999)) or (field  in (s1,s2,s3....s999))

另一种解决方法就是作为子查询。

select userid from LOG where id in( select id from LOG )

oracle in条件

An in_condition is a membership condition. It tests a value for membership in a list of values or subquery。

If you use the upper form of the in_condition condition (with a single expression to the left of the operator), then you must use the upper form of expression_list. If you use the lower form of this condition (with multiple expressions to the left of the operator), then you must use the lower form of expression_list, and the expressions in each expression_list must match in number and data type the expressions to the left of the operator. You can specify up to 1000 expressions in expression_list.Oracle Database does not always evaluate the expressions in an expression_list in the order in which they appear in the IN list. However, expressions in the select list of a subquery are evaluated in their specified order.

参考

6.14 IN Condition