Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

java.sql.SQLException: ORA-01000: maximum open cursors exceeded ?

 
Sam Doder
Ranch Hand
Posts: 204
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a whole bunch of records to insert into this table. I want to insert all the records and need setAutoCommit to be false since I need all the records to be inserted all at the same time using commit or rollback ,..etc.

I have a while loop that creates a preparedstatement and does executeUpdate()

something like this



But apparently I cann't go with more then 1000 rows which is strange because I seen people insert millions of rows at a time or at least hundreds of thousands.
I don't really understand what open cursors are causeing this issue. Does executeUpdate open cursors ?

Thanks for an help
 
Ram Narayan.M
Ranch Hand
Posts: 247
Chrome Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think Since more PreparedStatement instances are created at each loop and DB opens cursors for each newly created PreparedStatement in the Connection and exceeds the threshold... You can better do it with one instance of PreparedStatement instead of creating the instances at each loop....
 
Sam Doder
Ranch Hand
Posts: 204
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks that was it.
Great help ..............YES
 
Jan Cumps
Bartender
Posts: 2608
14
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Nice catch, Ram!
 
Consider Paul's rocket mass heater.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic