I would like to understand the factors considered while putting the Business Logic in Java VS putting the Business Logic in a DB like Oracle using complicated SQLs or PL/SQL.

I would like to understand the factors considered while putting the Business Logic in Java VS putting the Business Logic in a DB like Oracle using complicated SQLs or PL/SQL.

One of the many reasons is portability of your code.

Once you start writing your logic in Oracle SPs - you stuck with it. It will be very expensive to migrate to the other DB (in case you have to support it of course)
But if you are ONLY Oracle shop - then it would not apply to you...

Another reason is that Java code executes much more effectively(faster) then stored procedure code.
This is true for NON DB related logic (like loops, array manipulation and so on...)

Also, you can deploy you business logic in the way that it could be clustered (e.g. EJB) with fail-over support and load-balancing...

I'm sure there are many other reasons as well to keep your logic outside of DB.

I agree with Ruslan that in most cases it's better to have business logic in Java (or in more general terms in you middle tier). However, if you have to do some very intense data processing then stored procs may be justified. Eg, I worked on a risk management system recently where many calculations involved looping millions of times with queries inside the looops. Of course, you'd want that kind of stuff to be as close to the data as possible...

Mind you, it's an exception rather than a rule, most systems should be fine with middle-tier processing.

I suppose that a major reason to move the business logic from the DB (2 tier client server architecture) to the app servers (3 tiers client server architecture) is horizontal scalability (more machines)

you still cannot run Oracle on more than 1 machine. So you have to go for the vertical scalability (more CPUs in one machine). this is expensive and does not scale very well

you can scale app servers almost linearly: 1 web server machine with the HTTP load balancer, 10-1000 app server machines with sticky sessions and 1 strong DB machine

question is if you need for that a session fail over, what does not need to be very friendly with the scalability

also e.g. Oracle is working now on the DB grid technology to allow the horizontal scalability

so your motivation is to move as much work as possible from DB machine to app server machines

you should use the PL/SQL if the SQL solution is not effective especially from performance reasons