Java Prepared Statement keeping cursor open after error

Question

I am writing a Java class to insert data to a database with an iteration. Although I can now insert data, I am struggling in handling Oracle errors. In this case, I have deliberately created a Primary Key Constrain error by trying to insert duplicate primary keys (I have pre-loaded the database with the same entries as I am trying to insert with Java)

So as expected, I get the "ORA-00001: unique constraint". However, the problem I am having, is that after 300 iterations, I reach a new error:"ORA-01000: maximum open cursors exceeded"

I guess the issue is that every failed executeUpdate() keeps a cursor open.

I have temporarily solved the issue by including a close() statement on the catch of the error. However, I am wondering:

Should the failed executeUpdate() not be closing the cursor?
Is there a better way I can close cursors on Exception?
Why does it return a null Exception?

My Java Code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestOracleError {
    private static Connection conn;
    public static void main(String[] args) {
        //Connect
        try {
            conn = DriverManager.getConnection("jdbc:oracle:thin:"+
                    "@XXX.XXX.XXX.XXX:1521:XXXX", "XXXX", "XXXX");
        }
        catch(SQLException e) {
            System.err.println(e.getMessage());
        }

        //Iterate insert
        for(int i=1; i<5000; i++){
            PreparedStatement pstmt=null;
            try {
                //Deliberate error on the SQL (Primary Key Constraint)
                pstmt = conn.prepareStatement("INSERT INTO DUMMY (ID) VALUES "
                        +"('"+i+"')");
                pstmt.executeUpdate();
                pstmt.close();            
            }
            catch(Exception e) {
                System.err.println("DB Error on iteration "+i+": " + 
                        e.getMessage());
                //If I add the following line then I can close the error!
                //try {pstmt.close();} catch (Exception e1) {} 
            }    
        }
    }
}

http://stackoverflow.com/questions/4092914/java-try-catch-finally-best-practices-while-acquiring-closing-resources which is exactly what you are looking for. — QuakeCore, Sep 30 '15 at 08:26
Always do a not null check and close db resources in finally block. — gladiator, Sep 30 '15 at 08:28
Use try-with-resources. https://blogs.oracle.com/WebLogicServer/entry/using_try_with_resources_with as described by @Java-ledge. Connection, PreparedStatement and ResultSet are all auto-closeable. — ibre5041, Sep 30 '15 at 08:55
possible duplicate of [How should I use try-with-resources with JDBC?](http://stackoverflow.com/questions/8066501/how-should-i-use-try-with-resources-with-jdbc) — ibre5041, Sep 30 '15 at 08:57

Davide Lorenzo MARINO · Accepted Answer · 2015-09-30T08:29:20.937

If you need to insert many rows put the creation of prepared statement outside the loop and set only the values inside the loop.

// Moved outside
PreparedStatement pstmt=null;

// Using the question mark as a placeholder for a variable
pstmt = conn.prepareStatement("INSERT INTO DUMMY (ID) VALUES (?)");

for (int i = 1; i < 5000; i++) {

    try {
        //Deliberate error on the SQL (Primary Key Constraint)
        // Only set the variable in the loop
        pstmt.setInt(1, i);
        pstmt.executeUpdate();

    } catch (Exception e) {
        System.err.println("DB Error on iteration "+i+": " + 
                    e.getMessage());
        //If I add the following line then I can close the error!
        //try {pstmt.close();} catch (Exception e1) {} 
    }    
}
pstmt.close();  // Moved out of loop

Note: Your code don't close the pstmt if an exception happens. So statements remains opened. This can potentially create the problem of too many open cursors.

Generally the best solution is to close the resources in a finally block or use a try with resource statement

score 2 · Answer 2 · answered Sep 30 '15 at 08:42

Use finally or Try with resources.

try {
        //Deliberate error on the SQL (Primary Key Constraint)
        // Only set the variable in the loop
        pstmt.setInt(1, i);
        pstmt.executeUpdate();

    } catch (Exception e) {
        System.err.println("DB Error on iteration "+i+": " + 
                    e.getMessage());
        //If I add the following line then I can close the error!
        //try {pstmt.close();} catch (Exception e1) {} 
    }    
    finally
    {
        pstmt.close();
    }

or

try (Connection con = DriverManager.getConnection(myConnectionURL);
             PreparedStatement ps = con.prepareStatement(sql);) {
            ......
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }

user207421 · Answer 3 · 2015-09-30T09:44:09.440

I guess the issue is that every failed executeUpdate() keeps a cursor open.

No, the issue is that you aren't closing your PreparedStatements if an SQLException occurs.

Should the failed executeUpdate() not be closing the cursor?

No.

Is there a better way I can close cursors on Exception?

Close the PreparedStatement in a finally block, or with the try-with-resources syntax.

Why does it return a null Exception?

It doesn't. It throws an exception with a null message.

Instead of preparing 5000 PreparedStatements you should also investigate batches.

Java Prepared Statement keeping cursor open after error

3 Answers3