今天软件出了一个Bug,报出ORA-01795: maximum number of expressions in a list is 1000 错误,
才知道Oracle的In-list有1000个元素的限制。若把条件分成多个少于1000的IN:SELECT * FROM DUAL T WHERE T.DUMMY IN ('1', '2', '3',...,'1000') OR IN ('1001', '1002', ..., '2000') OR ... 即可解决这个异常:
才知道Oracle的In-list有1000个元素的限制。若把条件分成多个少于1000的IN:SELECT * FROM DUAL T WHERE T.DUMMY IN ('1', '2', '3',...,'1000') OR IN ('1001', '1002', ..., '2000') OR ... 即可解决这个异常:
- StringBuffer sb = new StringBuffer();
- for (int i = 0; i < columnContents.size(); i++) {
- if (PluginConst.EMPTY_STRING.equals(columnContents.get(i))) {
- continue;
- }
- if ((i % 1000) == 0 && i > 0) {
- sb.deleteCharAt(sb.length() - 1);
- sb.append(") OR " + columnName + " IN ( '"
- + columnContents.get(i) + "',"); // resolved
- // resolved ORA-01795 problem.
- } else {
- sb.append("'" + columnContents.get(i) + "',");
- }
- }
- // delete the last comma
- sb.deleteCharAt(sb.length() - 1);
- String selectSQL = "SELECT * FROM " + tableName + " WHERE "
- + columnName + " IN ( " + sb.toString() + " )";