How is your application going to pass data to the backend for batch processing ?
I personally prefer PL/SQL in Oracle over java stored procs. PL/SQL unlike Java is tightly integrated with SQL and was developed for that sole purpose. If DB independence is not a requirement... you will benefit from SP's. Unless you are doing one insert / update from java client at a time you won't see any benefit over SPs. All SQL statements fired from Java are dynamic by nature. PL/SQL can take advantage of explicit cursors where appropriate. Plus if you are planning to invoke your procedure from Client (middleTier.. lets say using execute batch) then calling a Oracle Stored Proc will be advantegeous.. as there wont be context switch from PL/SQL to Java for every statement you execute. From maintenance point of view - if DBA's are going to maintain it then , I think they will like simple PL/SQL. You dont need to build SQL statememnts in Strings dynamically. SQL in
JDBC code isn't really readable compared to PL/SQL.
Though one cannot generalize any rules -
1. Use PL/SQL for SQL intensive stuff
2. Use java where PL/SQL isnt the natural choice - Sending Email, FTP, getting access to OS related things.
3. If you cant do it in Java then use C external routine.
Thats what we follow in our projects.
With 9iR2 you can also natively compile your SP in oracle if you are doing computive intensive tasks.
