Please help: maximum open cursors exceeded

I had an web application to add filenames (string type) into a table. Some times I need to add 60000 filenames into this table. This table has a trigger. Whenever a new filename added, it assigns an ID to it.

When I tested it, there is an oracle error :maximum open cursors exceeded. Please help me to solve this problem. Did I add two many into this table? should I remove the trigger?

thanks.

You don't say how you add the data. Are you using a SQL script, some kind of GUI SQL frontend, a self-written program. If the latter which programming language?

But usually this happens when you don't close/free resources in your client code, e.g. in JDBC this would be Statement.close() and ResultSet.close() which - when missing - can cause this error.

Thomas

I used JDBC and JSP. A jsp page gets the list of filenames then it opened a connection and execute some sql statements (60000 insert statement, for example). then it closed connection.

I figured it probably because the number for open cursors are too low. I can either increase the open cursors or in my code, I closed the connection after a number of insert statement executed, and reopen another one.

Does higher number of Open Cursors good or bad?

Do you explicitely close the statements (and ResultSets) before closing the connection?

I didn't use ResultSets. My code is lik this:

*********************
Driver MM_driver = (Driver)Class.forName(MM_editDriver).newInstance();
Connection MM_connection = DriverManager.getConnection(MM_editConnection,MM_editUserName,MM_editPassword);
for(i=1; i< totalNum; i++) {
PreparedStatement MM_editStatement = MM_connection.prepareStatement(qstr[i]);
MM_editStatement.executeUpdate();
}
MM_connection.close();
***********************

Maybe I should remove the "PreparedStatement" line out of the For loop.

I also tried using Perl to do 60000 inserts, and it was fine. So it is the problem with limited number of open cursor. Something is wrong with the java cod.