This is a classic developmental question, and any contribution is always just a small view of the ongoing debate, external factors often play a larger part. As a general rule it is true that the closer to the database the more easy it is to enforce business rules, it is easier to have a constraint go unviolated if it is defined at the table level rather than at the application level. Stored procs also afford you centralized security, there are lost of resource you can look up which describe this approach. I think it really boils down to answering a few questions:
1 – Do you have the DBA resources do carry out this work now,
and will that resource be there once the project goes live ?
My guess is you’ll have a Prod DBA but not a dev one, whilst you’ll always need the J2EE developer. You also be tied to Oracle, not a bad choice but tied nonetheless
2 – Does your tools provide adequate and well optimized code?
If WebSphere generates good SQL and doesn’t parse 100 times per page then you can use that but I’ve found that lots of tuning time is taken removing , 100 SQL for 100 lines of display, type issues.
3- Are your developers as good at writting SQL as your DBAs?
Some are some aren’t, experienced RDBSM developers are often better than DBAs, again this goes back to the resource ratio you have and how many developers versus DBAs you have. It’s pointless putting loads of logic in the DB if you have to teach PLSQL to 20 J2EE staff.
The nicest approach I’ve come across sees all the SQL stored in XML files, that way both developers and DBAs can read them easily and spot issues quickly. I would then leave the stored proc aspect to areas where large amounts of data are worked on and network traffic would be harmfull. The RDBMS resources can then be used for constraints, indexes, tuning and monitoring SQL, efficient dataloads and that is usually enough to keep them busy. The important thing is that your database is designed well enough so that regardless of the datasource, data remains clean and as close to the business rules as possible.
That’s my 2 cents.