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

After first update in oracle loop ends

 
Arul Narayanan
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am using a loop to update a table(Oracle) multiple times, but after the first update it exits the loop. If i print the statement, loop does not end. What am i missing?
 
Knute Snortum
Bartender
Pie
Posts: 2902
62
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welcome to the Ranch!

Could you post the loop and use code tags. Thanks.
 
Arul Narayanan
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
 
Arul Narayanan
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
 
Dave Tolls
Ranch Hand
Posts: 2100
15
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Do you get any errors?

You are re-using the statement object while looping over a result set that was created in the original statement.

Re-using the statement will kill the result set of the previous statement and should have resulted in an exception.
 
Arul Narayanan
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks a lot Guys. I solved by using PreparedStatement.

 
khadar valli
Greenhorn
Posts: 22
Java MySQL Database Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Instead of using loops on the statement(Query), Its better if you can use batch update for this.
 
Paul Clapham
Sheriff
Posts: 21567
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Even better if you just use a single update statement which applies to all of the affected rows:

 
Knute Snortum
Bartender
Pie
Posts: 2902
62
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm not sure how the OP can set sal = 5000 for the first emp > 50, then 5001 for the second emp > 50, and so on, with the SQL you are suggesting.
 
Paul Clapham
Sheriff
Posts: 21567
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Okay, so I missed the "i = i + 1" bit there. Can't imagine why you would want to add sort of random amounts to people's salaries like that, but I've given up being surprised at the homework assignments that people describe here.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic