ORA-01000 maximum open cursors exceeded

[Deepali Gupta]

We are encountering the problem of "maximum open cursors exceeded"
exception.

Our web based system uses jdk1.3 as frontend with Oracle 9i as backend
and Oracle JDBC Driver version - 9.0.2.0.0 for connectivity.

Connection pooling is being used and the resultset is fetched in the
functions and returned to JSPs where the functions are being called.

As per the articles on the net this exception occurs as a result of
not closing the prepared statements/ statements/ resultSets.

The problem is how do we close the statements as we are using the
resultsets in the JSPs. We cannot close the prepared statements/
statements/ resultSets in the JSPs as we cannot access them because
they are local to the respective functions. Nor can we close them in
the functions as we are retrieving the records from the resultsets in
our JSPs.

Kindly reply with a solution as soon as possible.

[Dan White]

Deepali ,

I would select your results into a java object(i.e.hash table) in a
serverlet then close the cursor.
"Deepali Gupta" <de***********@yahoo.co.uk> wrote in message
news:c1**************************@posting.google.c om...

We are encountering the problem of "maximum open cursors exceeded"
exception.

Our web based system uses jdk1.3 as frontend with Oracle 9i as backend
and Oracle JDBC Driver version - 9.0.2.0.0 for connectivity.

Connection pooling is being used and the resultset is fetched in the
functions and returned to JSPs where the functions are being called.

As per the articles on the net this exception occurs as a result of
not closing the prepared statements/ statements/ resultSets.

The problem is how do we close the statements as we are using the
resultsets in the JSPs. We cannot close the prepared statements/
statements/ resultSets in the JSPs as we cannot access them because
they are local to the respective functions. Nor can we close them in
the functions as we are retrieving the records from the resultsets in
our JSPs.

Kindly reply with a solution as soon as possible.

[sybrandb]

"Dan White" <da*@omegaconcepts.net> wrote in message news:<ex*****************@nwrdny01.gnilink.net>...

Deepali ,

I would select your results into a java object(i.e.hash table) in a
serverlet then close the cursor.

Which is obviously a completely undesirable solution as you won't see
any changes to the table anymore. The OP just needs to increase the
init.ora parameter open_cursors and bounce the database.
I would strongly urge you to learn something about relational
databases.
Sybrand Bakker
Senior Oracle DBA

[Erik Domstad]

sy******@yahoo.com wrote in message news:<a1*************************@posting.google.c om>...

"Dan White" <da*@omegaconcepts.net> wrote in message news:<ex*****************@nwrdny01.gnilink.net>...

Deepali ,

I would select your results into a java object(i.e.hash table) in a
serverlet then close the cursor.

Which is obviously a completely undesirable solution as you won't see
any changes to the table anymore. The OP just needs to increase the
init.ora parameter open_cursors and bounce the database.
I would strongly urge you to learn something about relational
databases.
Sybrand Bakker
Senior Oracle DBA

If the problem is that statements aren't closed properly, increasing
open_cursors will only make it last a little longer before you have the
same problem again. If you're lucky the connection pool will clean up for
you but that solution is pure symptom fighting and will not help you.
Erik