Hmm, some more details... o.k.
The only possibility to organize my code in PL/SQL (I think it will be the same for TSQL) is to use packages. In Java you can also use packages to organize your code, but packages can have subpackages and classes are also used to keep code together, which belongs together..
-> So Java has a clear advantage in organizing/structuring code...
In Java you usually program against Interfaces, the Implementation behind the Interface can be changed (e.g. by Dependency Injection) at Runtime. One could argue that a package header is a kind of an interface for your package and you program against the package header, not the body. One could also argue that by using Dynamic SQL the implementation could also be changed at runtime in PL/SQL. But this isn't the same and you will never have the flexibility of Java in PL/SQL.
Another Issue is the way your code will be called. If you ever have tried to publish a Webservice in PL/SQL and in Java you will see the difference. And if you want to consume a Webservice it's nearly the same (yes, it works well in PL/SQL, too, but with some severe limitations, like only one
SOAP style was supported, I don't remeber if it was RPC or document). I have to admit that I did these tests with Oracle9i, maybe the handling improved, since Webservices get more and more important nowadays.
But it'S not just Webservices, in Java I can call the logic via RMI, via IIOP, via Webservice... You could say that if I have to call my PL/SQL logic I could use ODBC,
JDBC, ADO, DAO,... But that's not the same... Additionally I encountered much more problems when calling PL/SQL logic with different versions of the database and especially of the driver used to connect. Some times CLOB/BLOB fields were cut after 32k, sometimes Timestamps didn't work, and so on.
Of course potential problems are inherent to every interface , escpecially between different technologies. I also had some hard times when I first used IIOP and yes,we had to fix the CLOB truncation problem anyway, but IMO it's worse with the different techniques to connect to the DB.
-> IMO there is a clear advantage in the flexibilty of accessing the logic and especially if your logic has to access other logic if you use Java
Another point that I've encountered is that it'S much easier to code in a team if you have hundreds/thousands of classes than it is if you have some ten PL/SQL packages, since the risk of a collision is lower.
Maybe your project is small and you're the only one who works on it so you're thinking that this doesn't apply in your case, but projects may grow and then require a full team.
About the diverging code between single installations:
With the logic written in PL/SQL one can easily change the logic on a productive installation, either to add a feature that is only applicble to this installation or - more common - to fix an urgent bug. Especially the latter one is a real advantage of PL/SQL - at least at the moment.
The problem is that it requires a severe discipline to merge back those code changes to your development environment or it requires the same discipline to forbid changesat productive systems,especially when a complete installation isn't running, every minute downtime costs lots of money and you know that the bug fix is just a one liner which you could do in 5 seconds. In Java you have to compile it anyway, so you have to do it in your development environment right from the start and deploy your new jar/ear to the production machine. If it's not so easy (yes, I know it's possible, but in PL/SQL it's much easier) to do a quick fix nobody can force you to do it.
An additonal issue is automatic building/testing. I've seen much solutions for Java, including mockups for datasources, etc. (like
JUnit, DBUnit,..) but I've never seen anything like this in real operation for PL/SQL. There is SQLUnit and three years ago I had a short look on it, but then it didn't look user friendly. I just visited the homepage
SQLUnit and it looks much better now.
Having listed lot's of issues which in my opinion are obvious advantages of using Java I have to make some concessions in favor of PL/SQL
- there is usually far less overhead, especially for small projects
- if you are not experienced in Java it may be a steep learning curve with some severe drawbacks untill you have your first application ready for operation
- if you have to handle lots of data in very limited time: the overall performance of good PL/SQL is higher than the one of Java code if data has to be retrieved/written very often (like SQL has an advantage over PL/SQL)
And a last comment: all this is just my point of view. Feel free to agree/disagree with every single point. Just think about them for a moment.
I hope this was elaborate enough
And as said before: Feel free to agree/disagree with every single point.
John
