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?
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.
0
