There is one error which comes occasionally in our oracle database, but a hazaourdous and that is --> Maximum Open Cursors exceeded.

There are some explicit cursors used in our stored procedures. A procedure is simulated with an example below.

    RETURN Types.ref_cursor
    stock_cursor types.ref_cursor;
    OPEN stock_cursor FOR
          SELECT ric,price,updated FROM stock_prices
          WHERE price < v_price;
    RETURN stock_cursor;

The procedure is called by a Java program (thru JDBC) and the prepared statement is also closed at the end of the program in the finally block. I guess if we close the prepared statement then it will make sure every explicit cursor will be closed. But I think it is not happening.

When ever I try to query the no of cursors, it returns me more than 200 open cursors at any point of time. The max no of cursors set is = 300 FYI.

Please let me know how to close the open cursors.  Because I see that it cannot be closed inside the function as it returns the result set (cursor reference).

It would be great if anyone shall help to solve this problem asap as it is kind of a workstopper.

FYI..the functions are in a package.

Regards and Wish you have a good day,
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


Could you post a sample of your Java code?

You state that the prepared statement is closed at the end of the program, do you mean at the end of each call?  Although the Statement interface (which CallableStatement and PreparedStatement implement) declares that when you close the statement it should clear all of the resources, I've come across a couple of drivers that don't (but thankfully these are getting rarer!).

Are you sure that it is this query that is causing the problems?  

You can use the query:

SELECT sql_text, COUNT(sql_text)
FROM   v$open_cursor
GROUP BY sql_text

to find the most open cursors (if that makes sense!?), but it is not always an exact answer!

Also, I would normally recommend closing each item explicitly, the ResultSet's, etc (I know this may cause some outcry!), it's the old belt and braces aproach!  Each within its own try/catch block with checks for null.

(Note that I catch Exception instead of SQLException, as I've had problems when dealing with LOB's throwing NullPointerExceptions where the code was doing things it shouldn't and thereby not closing resources correctly!)


if (rset != null) {
try {
} catch (final Exception e) {

if (stmt != null {
try {
} catch (final Exception e) {

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
1. You should not use the finally block to reliably close cursors.
2. You should design your code to ensure that the resultSet/cursor and staements go out of scope after the close() method is invoked. This is necessary in order to execute garbage collection and reclaim the memory allocated for cursors

Turned out that you must close the statement as well
as the result set. Closing the statement only or the
result set only was not sufficient.

If you receive messages that you are running out of cursors or that you are running out of memory, make sure that all your Statement and ResultSet objects are explicitly closed. The Oracle JDBC drivers do not have finalizer methods. They perform cleanup routines by using the close() method of the ResultSet and Statement classes. If you do not explicitly close your result set and statement objects, significant memory leaks can occur. You could also run out of cursors in the database. Closing a result set or statement releases the corresponding cursor in the database.

Similarly, you must explicitly close Connection objects to avoid leaking and running out of cursors on the server side. When you close the connection, the JDBC driver closes any open statement objects associated with it, thus releasing the cursor objects on the server side.

Add this line to you init file and bounce your systems.


The parameter is now hidden and you need to use the underscore as:


Generally support has advised the consideration of the parameter if the application does not regularly close cursors as they will remain in the library cache until they are closed. The side effect is that you may close more cursors than you wish to, such as in the situation of nested cursors

here is good paper for Oracle cursor.
if you want to understand more trick, try to read it.
Can you check your init<SID>.ora file for these parameters?


in order to avoid the maximum open cursor exceeds error, you should have these two parameters set to:


And restart the database , run your application and see if this helps.

This solution worked for other users., take a look at this link, and I have prodived this solution.

Another solution is to use parameterized Cursor.
Instead of opening new cursors every loop, you reopen the cursor with the different parameters.
classical74Author Commented:
Many thanks for your inputs.I'll update you on the results soon.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.