Comparison between Java and PL/SQL :
Java can be used to construct stored procedures in much the same way that PL/SQL can. When to use Java? That is a question with a very simple answer. Use Java when not accessing the database and writing code which is computationally heavy. What does this mean? When answering a question such as this I always prefer to go back into the roots of a programming language or a database. In other words, what were PL/SQL and Java originally built for? What is their purpose? Let's start by looking at PL/SQL.
PL/SQL is effectively a primitive programming language and is purely an extension of SQL. SQL was originally built purely for the purpose of accessing data in a relational database. Therefore, it follows that PL/SQL is of the same ilk as SQL. PL/SQL was originally devised to create stored procedures in Oracle Database. Stored procedures were devised for relational databases in general, not just Oracle Database, to allow for coding of self-contained blocks of transaction-based SQL code. Those blocks of code are executed on the database server thus minimizing on network traffic. PL/SQL is much richer than stored procedure languages used in other relational databases. PL/SQL can be used to code complexity. This takes us to Java.
Why use Java? Java is an object-oriented programming language built for coding of highly complex front-end and back-end applications. If you know anything about objects at all you will understand that objects in programming are superb at handling complexity. It is in the very nature of objects to handle complexity by breaking everything down into its most simplistic parts. Java can be used to handle highly complex coding sequences and can be used to create Oracle Database stored procedures, much in the same way that PL/SQL can. Java is much more powerful than PL/SQL in doing lots of computations. Java is better than PL/SQL at anything that does not involve accessing the database, especially complex code. Coding requirements when accessing a database are trivial in relation to the complex routines and functions required by applications-level coding.
There is one small but fairly common problem with using Java. Java is object oriented. Oracle Database is relational. Object and relational methodologies do not mix well. Many Java applications, due to their object nature, break things into very small, easily manageable pieces. Object-oriented design is all about easy management of complexity. In relational terms management of complexity by severe break-down and object "black boxing" is an incredible level of Normalization. Too much Normalization leads to too much granularity and usually very slow performance. What commonly happens with Java applications is one or all of a number of things. These are some of the possibilities.
- Pre-loading of large data sets.
- Separation of parts of SQL statements into separate commands sent to the database. For instance, a SELECT statement could be submitted and then filtering would be performed in the Java application itself. This leads to lots of full table scans and a plethora of other performance problems.
- Sometimes object design is imposed onto the database to such an extent as to continually connect to and disconnect from the database for every SQL code execution. This is very heavy on database resource consumption.
So what's the answer to the nagging question of using Java or PL/SQL? The answer is to use both, if your skills set permits it. Java can be used to handle any kind of complex code not accessing the database. PL/SQL should be used to access the database. If you cannot or do not wish to use a mix and prefer Java then be aware that a relational database is not able to manage the complete and total "black box" breakdown into easily definable and understandable individual objects. Do not attempt to impose an object structure on a relational or furthermore even an object-relational database: they are not designed for that level of granularity.