Motivation for Using PL/SQL: Don’t Cut a Pizza with a Wrench – Resilient Software and PL/SQL

Motivation for Using PL/SQL: Don’t Cut a Pizza with a Wrench

I’m a big fan of stored procedures in general and PL/SQL in particular. Stored procedure technology provides many advantages over trying to implement complex database logic in application development languages such as Java, C#, JavaScript, and so on. I’ll discuss this later, but for now, just note that it’s not uncommon to see developers trying to shoehorn complex database logic into high-level languages.

The type of database logic I’m referring to here is where high-level code makes a large number of table-level changes across numerous databases, such as in a large batch job. The performance of this hybrid approach may be poor because it necessitates translation between the high-level language and the database environment. Worse yet is the issue of data consistency that can arise if a batch job only partially succeeds.

There may also be issues concerning excessive database locks, which can affect other users of the database (or databases). This is not to say that the hybrid approach is always bad. The point is that there are cases where the mix of high-level languages and complex database operations may result in a solution that is not resilient.

Perhaps even worse than this is the practice of using raw SQL (in high-level code) as a place for business logic. Using SQL in this way potentially misses the opportunity to incorporate the many useful PL/SQL abstractions and can result in brittle solutions. It’s a bad idea to try to cut a pizza with a wrench. It’s not that the wrench won’t cut the pizza; it’s just not going to lead to a good end result.

Before looking a little at some of the disadvantages of these high-level language approaches, I should note that I do also like object relational mapping (ORM) technology (such as Hibernate). I’ve used ORM in Java and C# (and even in Python) and I’ve written about it extensively over the years. ORM allows for a smooth experience of database technology in languages such as Java and C#. However, there are many cases where a PL/SQL solution may simply be a better option, particularly when we want to achieve resilient solutions.

PL/SQL code runs natively in the Oracle Database inside a dedicated runtime engine. This makes PL/SQL code a first-class citizen in the Oracle Database ecosystem. In other words, there is no need to translate from a Java (or other high-level language) layer into the database dialect. PL/SQL is itself a native Oracle Database technology, which means that it is optimized for this use and is tightly coupled with the database platform.

Another reason for favoring PL/SQL is security: the code runs inside the database. This reduces the likelihood of vulnerabilities such as leaking important data into Java logfiles. Other advantages of PL/SQL include speed and efficiency. As noted previously, with PL/SQL there is no intermediate high-level language technology layer. This is one reason why PL/SQL solutions will often substantially outperform an equivalent effort in Java or C#. The Oracle Database also works hard to optimize PL/SQL code. There are many benefits to using PL/SQL.

With a view to being as balanced as possible, is it hard to learn another method (other than PL/SQL), such as Java ORM?