Skip to main content

"Write once, run anywhere" - PL/SQL comes to yet another database!

Back in the late 80s, some big brains at Oracle realized that an operating system independent language was needed to build applications on top of Oracle Database (and our SQL implementation). Their rapidly growing customer base was writing applications in C (making calls to SQL via the OCI layer), and that was problematic. Code needed to be ported, re-tested, etc. for variations of the C compiler on different systems. And C wasn't very easy to write.

They dreamed of programs, running inside the database itself, which could be moved from operating system to operating system, and not require any changes.
"Write once, run  anywhere (there's an Oracle Database)."
Thus was PL/SQL born, as a sort of step-child of Ada (the syntax of PL/SQL was taken almost directly from Ada specs, to avoid having to invent an entirely new language).

It's such a great language that:
  • It offers the best performance for executing SQL in Oracle Database.
  • It provides a simple yet powerful way to implement complex business rules right in the database, where everyone knows such rules belong. :-)
  • It allows you to tightly secure your data from SQL injection and other vulnerabilities.
  • It powers Oracle Application Express, a fantastically popular low-code framework for mobile and web application development.
But more to the point of this blog, over the years other database vendors have found it important to support the compilation and execution of PL/SQL programs.

First, IBM added PL/SQL support to DB2 (now Db2 :-) ).

Next, EnterpriseDB complemented native postgreSQL's PL/pgSQL with "extensive support" for PL/SQL.

And now we have word that MariaDB, a fork from MySQL, is adding support for PL/SQL.

Now, on the one hand, I am a little bit dismayed, because the motivation for adding this support is not to salute the wonder of PL/SQL. It is to make it easier for customers to migrate their applications from Oracle database to Db2 or EnterpriseDB or MariaDB.

I sure don't want any of our customers to do that. Furthermore, I urge anyone considering such a thing to very carefully examine the claims of stated support for PL/SQL syntax, and test such claims thoroughly (compilation, performance, functionality). Definitely a "converter beware" situation.

But on the other hand, I am delighted that PL/SQL continues to be recognized as an element in Oracle Database applications critical enough to warrant significant development investment to support.

Congratulations, PL/SQL.


  1. Hello Steven,
    Last week a list was published about the 10 languages that developers hate the most

    And, of course, it is very nice to NOT see neither SQL nor PL/SQL on that list :)

    As about the migration from Oracle to other databases ...
    The problem is that everyone wants to live on this Earth ...
    and, as far as I understand, the main motivation for migrating away from Oracle
    is lower costs ... well ... I exclude here the "anti-Oracle-SAP-minded species" ...

    When it comes to comparing features available, I think that no other database can come anywhere close to Oracle.
    In an ideal world, all the forces would be united into further improving the best possible ones
    (language, database, or whatever), rather than inventing a new one every other day.

    Cheers & Best Regards,

  2. Next step - Oracle PL/SQL + Oracle APEX in Oracle MySQL.

    1. Lovely idea. Are you a MySQL user? Perhaps you could start up a petition at for the MySQL community. Get 50000 signatures and I bet the APEX team will pay attention. :-)

  3. Hi Steven,

    Well, for me that sounds like a good thing in general. For me as a developer that means I can leverage my PL/SQL knowledge on other databases which certainly increases my perspectives - now my knowledge does not only apply to Oracle Database but to PosgreS and MariaDB as well (with certain differences - SQL implementations aren't the same on every databases as well even though it is standardized). Maybe if there is a good implementation for Stored Program Units like PL/SQL available on more databases developers which previously hesitated to make use of them (platform dependand, unmaintainable,...and the whole "arguments") are using them more likely. The Thick Database paradigm might benefit from that...

    However; personally I've always wondered how that works from a Licensing perspective. First of all PL/SQL is owned by Oracle - so in general am I allowed to implement my very own PL/SQL compiler?

    And then the - IMO more critical part - EnterpriseDB's goal is to be 100% binary compatible -
    which also would include the Oracle Supplied Packages (DBMS_*, UTL_*) - and those are most certainly licensed somehow with the Oracle Database? I mean - sure, they'd need to reinvent the whole wheel for themselves (maybe there's pgSQL under the hood), but am I allowed to kind of "reuse" the names of your supplied packages and reimplement them bit by bit?


    1. I am not a lawyer and so will not to pretend to giving you a definitive answer. In fact, probably best to avoid answering at all. :-)

      Probably the best way to think about it is: no one's getting sued, so I guess it's OK.

      As for 100% binary compatible, well that is certainly the goal they should be aiming for. Gotta be hard.

      Thanks for sharing your view!

    2. A couple of month ago, I attended an EnterpriseDB event (yes, I know, shame on me) and this exact same question was asked by one of the attendees. I think the answer was that they don't call it PL/SQL. From what I understood, that name belongs to Oracle, but copying the syntax on the other hand and giving it a different name seems to be perfectly legal.

      I've to admit I was extremely surprised by how far their integration goes. It's not only similar to PL/SQL, it's almost an exact copy of it. As a developer you can really write code as if you were on an Oracle database. Pretty impressive. But as Steven said, their motives are purely profit based and their might also be issues in the follow up process. Every new Oracle version adds some new PL/SQL features. Will they implement those too, if at all possible? I also have some reservations about the performance aspect. At the base, it's still Postgres and as such, I imagine the PL/SQL support is an extra layer they added. Also, not everything is supported. One major bottleneck for me for example, was then fact they don't support Autonomous Transaction, which I use in about every piece of code I write for logging (tracing and error logging) purposes.

    3. Yes, playing catchup with new versions is tough.

      I wasn't aware of the lack of support for autonomous transactions. That *is* big. I wonder if that's hard in postgreSQL or they just haven't gotten to it yet.

  4. I don't want to sound cynical ... but today, when everybody is ready to buy everything rather than developing anything in house, I still want to see the company which will employ thousands of developers to recreate all of Oracle's functionality ...

    Of course it should be legal, I don't think that anyone can object to anything that you develop alone ... but I think that when you will be finished, probably the Earth will not have any more inhabitants ...


Post a Comment

Popular posts from this blog

Get rid of mutating table trigger errors with the compound trigger

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers.

In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply.

All the code shown in this example may be found in this LiveSQL script.

How to Get a Mutating Table Error

I need to implement this rule on my employees table:
Your new salary cannot be more than 25x th…

Table Functions, Part 1: Introduction and Exploration

August 2018 update: please do feel encourage to read this and my other posts on table functions, but you will learn much more about table functions by taking my Get Started with PL/SQL Table Functions class at the Oracle Dev Gym. Videos, tutorials and quizzes - then print a certificate when you are done!

Table functions - functions that can be called in the FROM clause of a query from inside the TABLE operator - are fascinating and incredibly helpful constructs.

So I've decided to write a series of blog posts on them: how to build them, how to use them, issues you might run into.

Of course, I am not the first to do so. I encourage to check out the documentation, as well as excellent posts from Adrian Billington (search for "table functions") and Tim Hall. Adrian and Tim mostly focus on pipelined table functions, a specialized variant of table functions designed to improve performance and reduce PGA consumption. I will take a look at pipelined table functions in the latte…

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post.

PL/SQL is a strongly-typed language. Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type).

PL/SQL offers a wide array of pre-defined data types, both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package).

Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types.

You can't really declare your own "user-defined" scalars, though you can define subtypes from those scalars, which can be very helpful from the perspective…