Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

CallableStatement in loop

 
Jason Bauer
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
I'm wondering what the best practices are for using a CallableStatement and the prepareCall function in a loop that is very large. The way it was currently coded it ends with the amount of cursors being exceeded. Thus I moved the creation of the CallableStatement outside of the loop and this seems to work but I'm wondering that the best practices are ? Here is an example of the code , please let me know what I'm doing wrong ? Should I be closing "myCallable" after each executeUpdate and if so will that affect the rollback or commit ?

try{
CallableStatement myCallable;
myCallable = conn.prepareCall("{ ? = call MY_FUNCTION_CALL(?,?,?,?,?,?) }");

while loop{

myCallable.setString(2, 'some value');
myCallable.executeUpdate();
}
}
catch(Exception e){
connection rollback;
}
finally{
connection commit;
}


Thanks
Jason
 
Bhagat Singh Rawat
Ranch Hand
Posts: 93
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Use executeBatch method.
 
Jason Bauer
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Will "executeBatch" work even if there are 120,000 rows ?
 
Bhagat Singh Rawat
Ranch Hand
Posts: 93
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jason Bauer wrote:Will "executeBatch" work even if there are 120,000 rows ?


I believe it should work Please check with "how executeBatch method works in JDBC"
 
Jason Bauer
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I tried it but I can't use it because we are using OUT variables in our Oracle function. Oh well , my code works as is today with the CallableStatement outside the loop. Unless anyone can tell me a reason to not do it the way it's currently coded I'll proceed.
 
Campbell Ritchie
Sheriff
Pie
Posts: 49733
69
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This thread would sit better in the JDBC forum. Moving.
 
Bhagat Singh Rawat
Ranch Hand
Posts: 93
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jason Bauer wrote:Unless anyone can tell me a reason to not do it the way it's currently coded I'll proceed.


Performance issue I guess.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic