File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC 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

Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
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: 189
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
internet detective

Joined: May 26, 2003
Posts: 29220

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.

[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
I agree. Here's the link:
subject: When to use stored procedure
Similar Threads
what are procedures in sql ?
Stored procedures in DB2/WAS
hibernate stored procedures insert scenario
Stored procedures in J2EE
How to call a stored procedure from java