java.sql.SQLException: ORA-01000: maximum open cursors exceeded / too many open cursors

This exception is caused by leaving too many open Statements and ResultSets. For example the following code throws such an SQLException.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Test {
    String url = "jdbc:oracle:thin:@<host>:<port>";
    String driver = "oracle.jdbc.OracleDriver";
    String user = "<userId>";
    String passwd = "<password>";
 
    public Connection getConnection() throws Exception {
        System.out.println("getting Connection");
        Connection conn = null;
     
        Class.forName(driver);
        conn = DriverManager.getConnection(url, user, passwd);
     
        return conn;
    }
 
    public static void main(String[] args) {
        Test test = new Test();
        Connection conn = null;
        Statement stm = null;
        String sql = "select distinct NAME from FRUIT";
     
        try {
            conn = test.getConnection();

            //Exception occurs in this loop
            while(true){
                stm = conn.createStatement();
                ResultSet rs = stm.executeQuery(sql);
                while (rs.next()) {
                    System.out.println(rs.getString(1));
                }
            }
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
}

The while(true) loop in the above code keeps creating new Statement and ResultSet without closing them, therefore generates such an SQLException.

To Fix the Problem, add the following code at the end of the while(true) loop.

            rs.close();
            stm.close();

So, the while(true) loop looks like this.

           while(true){
                stm = conn.createStatement();
                ResultSet rs = stm.executeQuery(sql);
                while (rs.next()) {
                    System.out.println(rs.getString(1));
                }
                 rs.close();
                 stm.close();
            }

-------------------------------------------------------------------------------------------------------------

                        
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.