File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes When to use stored procedure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "When to use stored procedure" Watch "When to use stored procedure" New topic

When to use stored procedure

Elizabeth King
Ranch Hand

Joined: Jul 11, 2002
Posts: 191
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

Joined: May 26, 2003
Posts: 33102

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.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
I agree. Here's the link:
subject: When to use stored procedure
jQuery in Action, 3rd edition