Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

When to use stored procedure

 
Elizabeth King
Ranch Hand
Posts: 191
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
We are using Oracle 9i in a J2EE app. The data architect wants to use stored procedures to handle some business logics. I think no stored procedures should be used at all.

Can anyone make some suggestions on when to use stored procedures?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 33694
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Elizabeth,
Stored procs are good when you are doing a lot of database manipulation. For example, get records from a bunch of tables with different table structures or unusual relationships and merge them into one record. While this could be done in JDBC, it would involve a tremendous amount of network traffic. Another case would be if performance testing shows a certain piece of the application to be the bottleneck.

I prefer to keep business logic out of a stored proc though because it ties you to a specific database. However, I wouldn't say "I think no stored procedures should be used at all." If you find a valid case for it within your application, you don't want to rule them out.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic