Rochelle Nicholas-Booth

Embracing PL/SQL Abstractions – Resilient Software and PL/SQL

Embracing PL/SQL Abstractions

Rather than trying to shoehorn business logic into SQL scripts, the inclusion of some fundamental PL/SQL programming abstractions allows for more powerful and resilient code constructions. For example, adding exception handling is one way of protecting your PL/SQL code from unexpected runtime exceptions, such as the attempted duplicate key insertion from “A Cautionary Tale”.

As you’ll see, well-crafted PL/SQL also facilitates observability, which helps avoid the problems just described where the critical sales data was missing. In other words, PL/SQL gives you the benefits of access to the constructs of a mature programming language. This in turn helps to move you away from such brittle SQL-based business logic and any need for handcrafted, error-prone data modifications.

Disadvantages of PL/SQL

What are some of the disadvantages of PL/SQL? All technologies have a total cost of ownership and there’s no such thing as a perfect language (or, indeed, a free lunch). A few of the disadvantages of PL/SQL are as follows:

  • PL/SQL is a legacy language.
  • PL/SQL is not a trendy language.
  • The use of PL/SQL may tie you to Oracle Database products.
  • People will ask why they can’t use SQL instead.
  • PL/SQL is often perceived as being difficult to learn.
  • Many developers don’t want to learn yet another language (i.e., developer fatigue).

These disadvantages are drawn from my own experience of PL/SQL in the various development teams I’ve worked on. Very often, a team is extremely well versed in languages such as Java, C#, and JavaScript. Having spent years becoming proficient in their language of choice, developers can typically produce code quickly and efficiently. Consequently, developers often prefer to stay in their preferred language and not have to worry about learning a new one, such as PL/SQL. In addition, time is usually strictly limited for development work. So, it’s no surprise that the bulk of developers like to remain on familiar ground.

This is understandable and it often motivates developers to search for rather creative ways of avoiding PL/SQL and doing things like mixing ORM and calling the PL/SQL stored procedures directly from Java or C#. This approach maintains their code in just the one language. However, if the data workflow is very complex, there may be severe performance issues with this mix-and-match approach. It’s also potentially a bit of an antipattern in that the high-level code and the PL/SQL become tightly coupled to each other—changing one may unexpectedly break the other. Breakages may only be detected by end users, and this can lead to disenchantment in the user community.

Calling stored procedures from Java or some other language may also reduce the security of the database. For example, stored procedure names and other important data may inadvertently get recorded in application logfiles. Another problem is that any errors or exceptions that occur in a stored procedure invoked from a high-level language may not be handled properly. As we saw earlier, there can be serious consequences when error conditions get overlooked.

When merging high-level languages with PL/SQL, perhaps worst of all is the lack of good separation of concerns. We are, in some sense, attempting to mix oil and water.