Avoid SQL Exceptions when Executing the Same Query Multiple Times

When you execute the same SQL query over a hundred times, you probably get exceptions similar to this one:

java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1 ORA-01000: maximum open cursors exceeded

The following code shows one reason you may be getting this exception. As you can see, the same PreparedStatement/Statement is being used after each SQL query execution, and the statement is never closed:

PreparedStatement pstmt = null...for (long i=0; i<600000; i++){ pstmt = connection.prepareStatement(...); pstmt.executeUpdate();}

In order to avoid this, make the following modifications to the code:

PreparedStatement pstmt = null...for (long i=0; i<600000; i++){ pstmt = connection.prepareStatement(...); pstmt.executeUpdate(); pstmt.close();}