GeeCON Prague 2014*
The moose likes Servlets and the fly likes Problem updating batch records in Oracle through Java - URGENT Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Java » Servlets
Bookmark "Problem updating batch records in Oracle through Java - URGENT" Watch "Problem updating batch records in Oracle through Java - URGENT" New topic
Author

Problem updating batch records in Oracle through Java - URGENT

peter brews
Greenhorn

Joined: Feb 11, 2002
Posts: 20
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
Ken Robinson
Ranch Hand

Joined: Jul 25, 2002
Posts: 52
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.
 
GeeCON Prague 2014
 
subject: Problem updating batch records in Oracle through Java - URGENT