Forums Register Login

CallableStatement in loop

+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
Use executeBatch method.
+Pie Number of slices to send: Send
Will "executeBatch" work even if there are 120,000 rows ?
+Pie Number of slices to send: Send
 

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"
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
This thread would sit better in the JDBC forum. Moving.
+Pie Number of slices to send: Send
 

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.
I've never won anything before. Not even a tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com


reply
reply
This thread has been viewed 4432 times.
Similar Threads
does Connection commit on close()?
Does a Connection commit on close?
Closing Statement object prior to committing
Difference between connection.commit and usertransaction.commit
StaleConnectionException
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 29, 2024 03:09:32.