Number of in operation SQL if more than 1000 prompts ORA-01795: The maximum number of expressions in the list is 1000
There are several ways in which induction occurs:
The first is that I mentioned in the previous [JDBC simultaneous query and delete operation] that in a transaction the in operations exceeded 1000, and the code was modified as follows:
Connection conn = null;
try {
Creating a Connection instance
conn = Jdbcutility.getfactory (). Createconn ();
Conn.setautocommit (FALSE);
Conn.settransactionisolation (connection.transaction_read_committed);
StringBuilder _STRBD = new StringBuilder ();
_strbd.append ("Delete from table1 where ID in (");
_strbd.append ("Select Sp_1.table1 from table2");
_strbd.append ("Sp_1,sp_tcontentinfo sp_2");
_strbd.append ("where sp_1.id=sp_2.id");
_strbd.append (")");
Statement stmt = Conn.createstatement ();
EXECUTE statement
int r = stmt.executeupdate (_strbd.tostring ());
Conn.commit ();
Stmt.close ();
Log.info ("Clean data successfully! ");
} catch (Exception ex) {
if (null! = conn) {
try {
Conn.rollback ();
} catch (SQLException se) {
Log.error ("Cleanup data rollback failed!") ");
}
}
Log.error ("Cleanup data failed with error message:" +ex.getmessage ());
} finally {
if (null! = conn) {
try {
Conn.close ();
} catch (SQLException se) {
Log.error ("Clean data, shut down database failed!");
}
}
}
The second type: in a separate query
The amount of data in the SQL must not exceed 1000
Workaround:
For example
Select * FROM table_name where col in (' col1 ', ' col2 ' ...)
If the item in the back is too large, this error will occur over 1000.
The workaround is to:
Select * FROM TableName where col in (' col1 ', ' col2 ' ...), ' col1000 ') or col in (' col1001 ', ......)
It would be nice to have a little bit of attention when building SQL statements.
Java JDBC Oracle ORA-01795: The maximum number of expressions in the list is 1000