Oracle Tutorial: Why ORA-01795 and Solutions

Find the SQL statement that is executed when an exception is thrown. It is a common SQL statement with the following content: SELECT * FROMDUALTWHERET. DUMMYIN (

Find the SQL statement that is executed when an exception is thrown. It is a common SQL statement with the following content: SELECT * FROM DUAL T WHERE T. DUMMY IN (

The system reports an SQL exception. The content is as follows:

Java. SQL. SQLException: ORA-01795: maximum number of expressions in a list is 1000

The SQL statement executed when an exception is thrown is not surprising. It is a common SQL statement with the following content: SELECT * FROM DUAL T WHERE T. dummy in ('1', '2', '3 ',...), the only difference is that there are more primary key values IN the brackets behind the IN clause.

Look at the content given IN the ORA-01795 is the SQL statement expressions list to accept the maximum value is 1000, check the description of the ORA-01795, make sure that the problem lies IN the IN brackets IN the primary key value exceeds 1000.

There are two solutions: one is to use JOIN or EXIST, and the other is to use IN, but divide the conditions into more than 1000 IN: SELECT * from dual t where t. dummy in ('1', '2', '3 ',..., '123') or in ('123', '123 ',..., '123') OR...

As I personally feel that JOIN is not intuitive and the meaning of EXIST is not easy to understand, method 2 is adopted, with code:

15:56:17, corrected the code. Thanks to gouliming for promptly identifying errors in the code.

========================================================== ======================

StringBuffer sb = new StringBuffer ();

Int inNum = 1; // Number of assembled IN conditions
For (int I = 0; I

If (StringUtil. isEmpty (custNOs [I]) continue;

// Do not make a low-level error and write it here: if (I = custNOs. length)
If (I = (custNOs. length-1 ))
Sb. append ("'" + custNOs [I] + "'"); // SQL assembly. The last line does not contain ",".
Else if (inNum = 1000 & I> 0 ){
Sb. append ("'" + custNOs [I] + "') OR CUST_NO IN ("); // solve the ORA-01795 Problem
InNum = 1;
}
Else {
Sb. append ("'" + custNOs [I] + "',");
InNum ++;
}

}

String selectSQL = "SELECT * from customer t where t. CUST_NO IN (" + sb. toString () + ")";