open cursors

[dknight]

We are developing a new application using JDBC on Oracle 8.1.6 on Windows NT. After an UPDATE statement, there are around 50 open cursors generated. After closing the browser, the open cursors remain.

The developer has coded an explicit COMMIT to be run after the update statement. Unfortunately, the open cursors are not closed.

I have seen this problem on another application using ATG Dynamo.

Any ideas?

Thanks in advance.

[Raminder]

Cursors are not closed after Commit!

[dknight]

Thanks for your response.

Do you have any ideas on closing the open cursors?

Thanks in advance.

[sambavan]

Check the code and see whether he is opeing any cursors. And add the code to close the cursors explicitly. Other wise set CURSOR_SHARING=FORCE in your init.ora parameter and bounce the instance. Remember it would have its over head of huge soft parses ...

Sam

[molonede]

Error Message: "ORA-01000: maximum open cursors exceeded"
The number of cursors one client can open at a time on a connection is limited (50 is the default value). You do need to explicitly close the statement, by using the method stmt.close() in order to close and freeup the cursors.

If you dont close these cursors explicitly, you will get this error eventually. Simply increasing the "OPEN_CURSORS" limit can help you avoid the problem for a while, but that just hides the problem, not solve it. It is your responsibilty to explicitly close out cursors that you no longer need.

[dknight]

I am ignorant in regards to closing cursors.

Is there a simple command that can be incorporated into the SQL statements to close the cursors?

Thanks

[molonede]

I beleieve closeing the cursors is something your developer will have to incorporate into the web page.

[vinit]

Pls note that in plsql v2 the opened cursors are not closed ,. but cached. This may also cause a problem.
Which version of plsql r u running ?

Cheers.

[dknight]

The developers are using SQL*Plus with JDBC. There is no PL/SQL.

My understanding is that a cursor is a piece of memory. After the statement is parsed, executed, and the changes are committed, I would think that the cursor would be closed.

How can the developers close these open cursors?

[dknight]

We may have found a solution.

When the Java code throws exceptions before the statement is fully processed, several open cursors occur.

The developer has reworked the code so that the throwing of exceptions is done after the SQL statement is fully processed.

Thanks