Problem updating batch records in Oracle through Java - URGENT
Joined: Feb 11, 2002
Hi i have a servlet which reads an excel file and converts it to a vector. after thar i insert the elements of vector into a Oracle table one at a time. If the file is small no problem. But when the file grows i get errors "ORA-01000 maximum open cursor limit exceeded". 1. In the loop of the Vector I have closed all the Statement, PreparedStatement objects so that Oracle cursors associated with it closes. 2. After the Loop I am closing the Connection (using weblogic Connection pool). I cannot close the connection in the loop because I will have to get the connection back in the loop which is very heavy. My question is even though I am closing all the statemets, preparedstatmets objects inside the loop (which is associated with one insert) why do the cursors associated with them do not get released. In monitoring Oracle i could clearly see that the number of cursors are rising up and they never get released even if the statement objct is closed. i do not want to increase the Parameter in ora.int to bump up the number of cursors. i could use batchUpdate but I have to do some database validations before every insert which cannot be done in the batchUpdate Any suggestions
Joined: Jul 25, 2002
Just a quick guess, try executing a 'commit' every so often. If Oracle believes that the data has not been committed, it may keep the cursor available for rollback purposes.