Home » Tip Bank » Testing and QA » error » Exception Handling

Language: Java
Expertise: Beginner

May 11, 2004

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();
}