Skip to Main Content
  • Questions
  • Regarding the Current Role of PL/SQL in Modern Technology Stacks


Question and Answer

Connor McDonald

Thanks for the question, Dhrub.

Asked: January 29, 2024 - 5:44 am UTC

Last updated: February 26, 2024 - 5:53 am UTC

Version: oracle 23c

Viewed 1000+ times

You Asked

Dear Team,

I hope this message finds you well. I have been reflecting on the current landscape of PL/SQL and its role in contemporary technology stacks. I would greatly appreciate your insights on a few points that have been on my mind.

PLSQL for Business Logic ?

While it's widely acknowledged that "as long as there is Oracle, there will be PL/SQL," I am eager to explore forward-looking scenarios where PL/SQL remains a prominent choice for business logic. In today's context, it seems that business logic is predominantly implemented using modern object-oriented languages such as Java or .NET, leveraging features like Streams and Lambda functions. Could you provide examples or use cases where PL/SQL excels and is considered integral, especially in comparison to these object-oriented approaches?

PLSQL for Data Engineering ?

The ETL landscape has witnessed a significant shift towards technologies like Spark for seamless integration with data warehouses and data lakes. In this evolving scenario, I am curious to understand how PL/SQL continues to play a vital role in ETL processes. Are there specific use cases or examples where PL/SQL is still the preferred choice in modern data engineering stacks?

I understand the historical significance of PL/SQL in minimizing network calls and maintaining code proximity to databases, as highlighted in research papers advocating for a thick database approach. However, I am keen to bridge the gap between theoretical advantages and practical implementations. Are enterprise projects aligning with this approach, or is the trend shifting towards business logic predominantly residing in Java/.NET environments?

In essence, could you kindly furnish examples and use cases illustrating where PL/SQL stands out as a core, integral component in modern data engineering or application development stacks?

I am particularly interested in understanding if PL/SQL is now primarily considered a supplementary or exception-use language, driven by compliance requirements rather than intrinsic value in data movement scenarios.

I appreciate your time and insights into this matter, and I look forward to hearing from you soon.

and Connor said...

I'll answer both in one take.

The industry has (finally) started to see the benefit of running data-related code as close to the data as practically possible. I've lost of the number of client side frameworks that now support "server side" processing.

I do believe that historically we were perhaps a bit overbearing with our "SmartDB" approach because people tended to take from that that the *entire* application should be based solely in PLSQL, and that anything was an error. That wasn't our intent - it was more about "right tool for the right job". And if you're planning on dragging terabytes of data out of your database to somewhere else, just so you can process that data - you're using the wrong tool.

It was never about PL/SQL "the language", it was about "use a tool that is close to the data so that the data does not have to move".

If you want an entertain and more in-depth read on this topic, here's an excellent resource

Even though its dated now, the premise remains similar. Run data code near the data and in the best SQL integrated language, run UI related code in the best UI code.

A real life example I've used in many talks - a customer I worked with were having numerous performance problems with their C++ built applications. We refactored maybe 10% of the code into a more plsql centric model and resolved all of their issues. C++ was the ideal solution for 80% of their app, but not 100% of their app.


  (2 ratings)


PL SQL for business logic ? Data intensive i agree its fine

Dhrub, February 21, 2024 - 3:10 pm UTC

I am completely aigned to your view , its a good choice to use plsql for data heavy operation . .that answers my 2nd question on data engineering

But i am not sure if you can answer the same for both , because in later your taking about Peta byte of data in the former you have transactional user based data . .it may be huge too .

But for Business logic i see almost all project implement the business logic in the code using Java 9 or .Net . .. . db is limited to only curd operation via the orm .

in that way do you shet light on same .
Connor McDonald
February 26, 2024 - 5:53 am UTC

I suppose I always chuckle when a dev team will say:

"This is BUSINESS logic so we're doing it in Java. Its not a DATA logic function"

and then I ask, "OK, what is the basic function" to which the reply is:

"We get some data from here, here, here, and here, and from there, and also here, and here, and here....and then we do BUSINESS logic. No data logic at all"


What is "business logic"?

Stew, February 22, 2024 - 11:16 am UTC

If the result of "business logic" is something that must be persisted, then it is actually "data logic".

It seems to me that "data logic" is of two kinds:

1) Rules governing data and data relationships that should always be respected, meaning constraints and "assertions"
(This kind of rule must be enforced whenever the data is created or changed, whether by "business logic" or "data engineering".)

2) Logical "units of work", meaning transactions.

The first kind should be implemented in DDL if possible. Where "assertions" are concerned, it's more complicated. A simple example of an assertion: in an employee table, only one row can have the job of PRESIDENT. I would say that PL/SQL or not, the implementation of this rule should be in the database.

The "smartDB" approach was all about transactions. It dictated that client code was never allowed to do SQL: all it could do was call transactional APIs written in PL/SQL. As Connor stated, there may be reasons to share the load with the client code.

I suspect that JSON Duality Views in version 23c could provide an alternative to PL/SQL in many cases, since they are APIs both for accessing and changing data.

The important things to me are to respect relational and transactional logic, and to avoid losing time in multiple round trips between the client and the database. If that's my job and the database is Oracle, then I want to know PL/SQL and SQL.

In my former company, stored procedures were outlawed and ORMs were mandated, but all the "devops" stuff (creation of tables, test data, etc.) was written in PL/SQL and SQL! Even if you take PL/SQL out of the application, you can't take it out of the project...

Best regards,
Connor McDonald
February 26, 2024 - 5:49 am UTC

Thanks for stopping by Stew. Always appreciate your input.