CODEWITHSUNDEEP
javaoracle-databasejdbc
akila
akila

Reputation: 697

java jdbc and oracle - maximum open cursors exceeded

I have some JDBC code that is retrieving data from Oracle.

In my method I use a prepared statement, however I don't close that prepared statement. To test it I ran this in a loop and sure enough I got an exception:

ORA-01000: maximum open cursors exceeded

My question is in case of a managed environment (code deployed on a Java EE Application Server using connection pools):

I'm assuming as connections in the pool are not really closed - the oracle session will be alive.

Upvotes: 4

Views: 18078

Answers (3)

Esteban Cacavelos
Esteban Cacavelos

Reputation: 753

You MUST close the Prepared Statement, otherwise you wont be able to execute more queries.

Suppose you have:

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

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

    ResultSet rs = ps.executeQuery();

    rs.close();
    ps.close();

Upvotes: 2

ziggy
ziggy

Reputation: 15876

The maximum open cursors error is generated on the oracle database. It is the database that has this limit. The application will keep sending the requests but the database will return the error.

When the maximum open cursors is exceeded, the application (i.e. the client) can still keep send the requests to the database but the database will just 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 the above is not fixing the problem. To fix it you need to close your connections/resultsets/PreparedStatements etc.

One possible scenario when your app server will not be able to send SQL requests is if the number of allowed active connections on your connection pool is less than the number of connections the database allows.

Upvotes: 1

Stephen C
Stephen C

Reputation: 719551

You need to close the ResultSet objects returned when you execute a query. And to make sure that you don't leak cursors, you need to do this in a finally block. I believe that this applies in a managed environment too.

Upvotes: 5

Related Questions