java sql SQLException - ORA-01000 maximum open cursors exceeded

Question

An ORA-01000 SQL exception is occurring for me. I ther, therefore, some questions about it.

1. How closely do the maximum open cursors relate to the amount of JDBC connections, and how closely do they relate to the statement and resultset objects we have created for each connection? (We are ututilizing connection pool.)
2. Is it possible to set the database's maximum number of statement/resultset objects (like connect single-threaded
3. In a single threaded environment, is it prefeutilizeo utilise instance variable statement/resultset object rather than method local statement/resultset object?
4. Does running a prepared statement while in a loop exacerbate this problem? Note: pStmt is closed once the loop has finished. (Of course, I could have used sqlBatch.)

{ //method try starts  
  String sql = "INSERT into TblName (col1, col2) VALUES(?, ?)";
  pStmt = obj.getConnection().prepareStatement(sql);
  pStmt.setLong(1, subscriberID);
  for (String language : additionalLangs) {
    pStmt.setInt(2, Integer.parseInt(language));
    pStmt.execute();
  }
} //method/try ends

{ //finally starts
   pStmt.close()
} //finally ends 

     5. What will happen if conn.createStatement() and conn.prepareStatement(sql) are called multiple times on single connection                   object ?

 

Answer 1

The maximum-open-cursors error, also known as ORA-01000, occurs frequently when developing Oracle databases. It occurs when a programme tries to open more ResultSets than there are configured cursors on a database instance in the context of Java.

Typical causes include

A configuration error

• Your application uses more threads to query the database than it does cursors. One example is when you have more connections and threads available than there are database cursors.
• You are using an excessive number of connections because you have numerous developers or applications connected to the same database instance, which likely contains numerous schemas.

Solution: 

• Increasing the number of cursors on the database (if resources allow) or
• Decreasing the number of threads in the application.

Hope this helps!

Earn Java Certification and become certified.

Thanks!