Java jdbc and oracle - maximum open cursors exceeded

Question

I have JDBC code that retrieves data from Oracle.

In my method, I use a prepared statement, but I do not close this prepared statement. To test this, I ran this in a loop and, of course, got an exception:

ORA-01000: maximum open cursors exceeded 

My question is in a managed environment (code deployed on a Java EE application server using connection pools):

• What is happening with the app?
• Can he never be able to run an SQL query into the database if the connection is not closed / reworked? (suppose there is only 1 connection in the pool)

I assume that the connections in the pool are not actually closed - the oracle session will be live.

Answer 1

You need to close the ResultSet objects returned when the query was executed. And to make sure you're not leaking cursors, you need to do this in the finally block. I believe this applies to a managed environment.

Answer 2

You MUST close the prepared statement, otherwise you will not be able to execute more requests.

Suppose you have:

  PreparedStatement ps = conn.prepareStatement("select * from my_table"); ps.execute(); ps.close(); 

You must run ps.close to avoid this problem. And as I said @Stephen, also close the ResultSet.

  ResultSet rs = ps.executeQuery(); rs.close(); ps.close(); 

Answer 3

The maximum error of open cursors is generated in the oracle database. This is a database that has this limit. The application will continue to send requests, but the database will return an error.

When the maximum open cursors are exceeded, the application (i.e. the client) can still send requests to the database, but the database will simply reject the request until the open cursors are closed.

You can increase the allowed open cursors using something like

 "ALTER SYSTEM SET OPEN_CURSORS=2000 SID='DUMMY'"; 

But this does not fix the problem. To fix this, you need to close your connections / results / PreparedStatements, etc.

One of the possible scenarios when your application server will not be able to send SQL queries is that the number of allowed active connections in your connection pool is less than the number of connections that the database allows.