MAX NUMBER OF CURSORS EXCEEDED ERROR

Hello,

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.

CREATE OR REPLACE FUNCTION sp_get_stocks(v_price IN NUMBER)
    RETURN Types.ref_cursor
AS
    stock_cursor types.ref_cursor;
BEGIN
    OPEN stock_cursor FOR
          SELECT ric,price,updated FROM stock_prices
          WHERE price < v_price;
    RETURN stock_cursor;
END;
/

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,
Balaji