So I started all of this by talking about how PLSQL is going to give you a performance boost, just by moving some of that SQL code into the database and out of your Java (Rust, Go, Javascript, Python, insert any app tier language here).

People then called me out on my benchmark setup, so I had to revisit the task with a more realistic configuration involving a network between the application tier and the database tier, plus some concurrent user activity. And of course, the results simply swung even more in favour of throwing some PL/SQL into your application coding mix.

Good friend of the Oracle community Franck Pachot then commented on a non-technical aspect of this discussion.

This is a common (and valid) statement but it applies not just to PLSQL but any component of a database technology stack that is vendor specific. Hence, my rebuttal to this is – at what point do you stop using elements of a database because they do not exist in facsimile in the majority of other databases? Would I never use the DATE datatype because some databases treat it as days on a calendar, whereas others allow a time component as well? What about all the SQL functions that are vendor specific? Clearly LISTAGG is off the table, because you will need to rewrite that if you move off Oracle. Or perhaps you’re on Postgres; then please don’t get excited about the chance to use nifty FILTER clause on aggregates because no-one else has that syntax.

In reality, if you build your apps with a singular focus on database portability, you end up with an application that uses the lowest common denominator of database functionality. Ultimately you are probably going to end up with a set of tables that only have string datatypes, and of course, you will need to make sure none of those table names are too descriptive because the maximum length of a table name also varies from database to database.

“Oh stop being facetious…” I hear you exclaim. “…There are different levels of porting effort, and going to PL/SQL to Java is going to be soooo much bigger than just converting SQL functions and datatypes”.

I agree that there is much more nuance to this topic than my simply waving my hand and saying “Don’t worry about it” but let us explore more by taking a look at some of the source code from the Swingbench benchmark. I’ve picked one of the APIs that is called as part of the Order Entry benchmark called “BrowseAndUpdateOrders”. This is just one of half a dozen or so routines that each get called as a standalone transaction when running the full benchmark. Let’s explore two possible scenarios:

• You are trying to squeeze more performance out of your application, so you want to port some of the Java code with database calls to be done in PL/SQL, or

• You are trying to port some existing PL/SQL routines back to Java because you don’t want to use PL/SQL.

In either case, would such a transition really be such an arduous process? I’ve put the same routines (in Java and in PL/SQL) side by side below. I removed from each the parts of the code that were related to the benchmark process (recording timings, counters, metrics etc) and just focussed on the parts of the code that do the database functionality required, namely, browsing and updating an order. I’ve had to embed some extra code for a sub-program in the PL/SQL side, because it is a touch more modular than its Java equivalent, but other than that it is a like-for-like comparison.

Now ask yourself how hard it would be to migrate between the two? I contend that refactoring the code in either direction is going to be a relatively straightforward task for any developer with a base level of competence in either language.

“Oh but real applications are more complicated” comes your rebuttal, but I would disagree.

Real applications in their entirety are indeed complex beasts, but when you look at the parts of an application that deal with data access, they are commonly even simpler than screenshots above. The complexities of data access typically lie not in the calling programs, but in the SQL queries they execute, and the SQL remains unchanged no matter what language you’re using. (I’d argue SQL is even easier in PL/SQL, because as you can see from above screenshots, no string formatting, no need to prepare and bind variables – it just works directly embedded in the code)

I stress I’m not idly pontificating here from some theoretical standpoint. I’ve got real evidence to back my claims up. The ability to easily transition between a 3GL and PL/SQL comes from an experience of mine with a real project team on a real business project. Before I joined Oracle, I worked with a team of C# developers who had no PL/SQL experience at all. However, throughout the life of the project they exploited the benefits of PL/SQL extensively, not by diving into the most niche complexities of the PL/SQL language, but by simply looking for opportunities where the C# layer was doing repeated network trips for data access and converting those into PL/SQL calls to eliminate the latency and lower the parsing overheads.

As I said in the first post a few days ago, anyone that tells you that language “X” should be used for 100% of the time is probably has some commercial skin in the game for that language. I’m not saying that PL/SQL should be the sole language for your development team, but if you’re using an Oracle database, then just a little PL/SQL knowledge is going to be an absolutely win for your applications.

And to reiterate, if you’re not on the Oracle database, the same logic applies. Find the language that runs closest to the data and your application (and hence your customers) will thank you for it.