Why this code throw SQLException: - ORA-01000: maximum open cursors exceeded

Question

For the following code, I insert 2400+ rows in a loop. I never close the connection until all is done. It throws an exception. What is wrong and what do I missed here?

    private PreparedStatement pst = connection.prepareStatement(INSERT_FAILED_INTERIOR_ROOM);
    private Connection connection = null;

    public static void insertIntoFaildIRoom(String master, String room, String xmlContent) {
        try {
            pst.setString(1, master);
            pst.setString(2, room);
            pst.setString(3, xmlContent);
            pst.executeUpdate();
            connection.commit();
            System.out.println("failed room insert commited");
        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException e1) {
                /* Nothing */}
            String errorMsg = String.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
            System.out.println(errorMsg);
        } catch (Exception ex) {
            System.out.println("Error when insert in failed room " + ex.getMessage());
        } 
    }


public String addResponse(){
    try {
        if (connection == null) {
            connection = MainConnection.open();
        }
        if (connection != null) {
            System.out.println("Connected to the database!");
            InteriorRoomBatch batch = new InteriorRoomBatch();
            batch.fillInteriorDetailsFromCache();
            for (InteriorDetails interiorDetails : batch.getInteriorDetailsList()) {
                addRoomQuery.insertIntoFaildIRoom(interiorDetails.getMaster(), interiorDetails.getRoom(), interiorDetails.xmlContent() );
            }
        } else {
            System.out.println("Failed to make connection!");
        }

    } catch (SQLException e) {
        // After inserting coupel of hundrands of row, it throw excetion  ORA-01000: maximum open cursors exceeded
        String errorMsg = String.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
        System.out.println(errorMsg);
    }finally {
        close(pst);
        connection.close();
        System.out.println("close the PreparedStatement after");
    }
    return "ok";
}

Don't prepare inside the loop, call `prepareStatement()` **outside** the loop just once. — , Aug 26 '19 at 12:56
Clean the indentation, I am pretty sure your `finally` clause is in the main `catch` clause. So you don't close the `pst` unless there is a problem. — AxelH, Aug 26 '19 at 12:59
@a_horse_with_no_name insertIntoFaildIRoom is called in loop form addResponse — masiboo, Aug 26 '19 at 13:08
@masiboo: correct, and that's why you prepare a **new** statement for **every** loop iteration. That is extremely slow and not how a prepared statement should be used. Prepare it **once** use it **multiple** times. — , Aug 26 '19 at 13:09
@a_horse_with_no_name Actually, I never used JDBC in JAVA. I always used ORM like Hibernate. I have o use JDBC now. I am learning it. I updated my code after u comments is it ok now? Please let me know? Is it correct way ? — masiboo, Aug 26 '19 at 15:35

Why this code throw SQLException: - ORA-01000: maximum open cursors exceeded

0 Answers0