Should one use PL/SQL or Java to code procedures and triggers?

Should one use PL/SQL or Java to code procedures and triggers?

Both PL/SQL and Java can be used to create Oracle stored procedures and triggers. This often leads to questions like "Which of the two is the best?" and "Will Oracle ever desupport PL/SQL in favour of Java?".

Many Oracle applications are based on PL/SQL and it would be difficult of Oracle to ever desupport PL/SQL. In fact, all indications are that PL/SQL still has a bright future ahead of it. Many enhancements are still being made to PL/SQL. For example, Oracle 9i supports native compilation of Pl/SQL code to binaries. Not to mention the numerous PL/SQL enhancements made in Oracle 10g and 11g.

PL/SQL and Java appeal to different people in different job roles. The following table briefly describes the similarities and difference between these two language environments:

PL/SQL:

• Can be used to create Oracle packages, procedures and triggers
• Data centric and tightly integrated into the database
• Proprietary to Oracle and difficult to port to other database systems
• Data manipulation is slightly faster in PL/SQL than in Java
• PL/SQL is a traditional procedural programming language

Java:

• Can be used to create Oracle packages, procedures and triggers
• Open standard, not proprietary to Oracle
• Incurs some data conversion overhead between the Database and Java type
• Java is an Object Orientated language, and modules are structured into classes
• Java can be used to produce complete applications

PS: Starting with Oracle 10g, .NET procedures can also be stored within the database (Windows only). Nevertheless, unlike PL/SQL and JAVA, .NET code is not usable on non-Windows systems.

PS: In earlier releases of Oracle it was better to put as much code as possible in procedures rather than triggers. At that stage procedures executed faster than triggers as triggers had to be re-compiled every time before executed (unless cached). In more recent releases both triggers and procedures are compiled when created (stored p-code) and one can add as much code as one likes in either procedures or triggers. However, it is still considered a best practice to put as much of your program logic as possible into packages, rather than triggers.