* You should do it in a single SQL statement if at all possible.
* If you cannot do it in a single SQL Statement, then do it in PL/SQL.
* If you cannot do it in PL/SQL, try a Java Stored Procedure.
* If you cannot do it in Java, do it in a C external procedure.
* If you cannot do it in a C external routine, you might want to seriously think about why it is you need to do it…
think in sets… learn all there is to learn about SQL…
Analytics rock and roll -> http://asktom.oracle.com/pls/ask/search?p_string=Analytics+rock+and+roll
Reference : http://tkyte.blogspot.com/2006/10/slow-by-slow.html
Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog 
regarding: You should do it in a single SQL statement if at all possible
I have to disagree. I have written single SQL statements that were pages long and found later that I really regreted doing so. The problem with doing everything in a single statement is debugging. When the results of your query are not right and you need to figure out why, with a complex query you are out of luck. If you break the query down into several smaller queries the debugging becomes easy.
I would say, “you should use PL/SQL and break the code into small pieces UNLESS performance is a problem. If performance is a problem, then start combining the smaller SQL statement where possible”
regards,
Mike
hicamel thank you for your comment, I totally agree with you in terms of flexibility and maintainability of the developed application.
But there are sometimes some constraints you have to obey the performance needs for example of a legal problem. I experienced several examples, until it was over I didnt wanted to go for the analytics solution where pipelined plsql should handle, but at the end it was a legal issue and it had to be finished within some time frame that we had no alternative but anaytics.
Cost was of course inevitable after some change needed, it was death of pain to modify some several pages of analytics for the new requests.
Best regards,
Tonguc