java jdbc and oracle - maximum open cursors exceeded

Question

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):

• What happens to the application?
• Will it never be able to fire any SQL query to the database unless the connection is closed / recycled? (assume that there is only 1 connection in the pool)

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

Answer 1

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.

Answer 2

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();