It will depend on your working environment and the nature of the project. Some places try to avoid putting much logic into the database using proprietary languages like PL/SQL, because they want to be free to switch to another RDBMS platform without having to re-implement their code. Other places reckon that they're committed to Oracle e.g. by licence agreements, strategic decisions etc, so they might as well make full use of all it can offer. Meanwhile, some projects don't require much data-oriented logic and can be implemented with standard Java tools and ORMs etc, while others need to implement a lot of heavy duty data-centric processing which really does belong as close to the data as possible. Of course, there's a lot of ideological discussion around these points, so it also depends who you ask!
FWIW, I'm making a similar transition right now. I worked mainly on Oracle applications for over 20 years, but found in recent years that I was getting pigeon-holed in an increasingly limited corner of the market, so I've now started a new job where I will be working primarily with Java and (initially) PostgreSQL. As an ageing member of
Generation X I'm calling it
recurving to make it sound more positive
. But I reckon any serious Oracle-based system will be able to use somebody with good Oracle skills as well as good Java - there is still a real shortage of good Java people who really understand relational data. Well, I hope so!
As for when to use PL/SQL, SQL or another language like Java, here's Tom Kyte's view from "Expert Oracle Database Architecture" (2nd edition), p.3:
Tom Kyte wrote:* You should do it in a single SQL statement if at all possible. And believe it or not, it is almost always possible.
* If you can't do it in a single SQL statement, do it in PL/SQL - as little PL/SQL as possible! Follow the saying that goes "more code = more bugs, less code = less bugs".
* If you can't do it in PL/SQL, try a Java stored procedure. The times this is necessary are extremely rare nowadays with Oracle9i and above.
* If you can't do it in Java, do it in a C external procedure. This is most frequently the approach when raw speed or using a third-party API written in C is needed.
* If you can't do it in a C external routine, you might want to seriously think about why it is you need to do it.
I've never had any need to resort to using a Java stored procedure, and I can't remember the last time I had to work with external C code. Of course, Kyte's comments apply specifically to data-oriented processing, especially when dealing with large volumes of data - querying, sorting, filtering, moving etc. Your web front-end is unlikely to be coded in SQL, after all!
