"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.

Comments

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

    https://www.techrepublic.com/article/the-10-programming-languages-developers-hate-the-most/?bhid=85372571&ftag=TREe09998f

    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,
    Iudith

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

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

      Delete
  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?

    cheers

    ReplyDelete
    Replies
    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!

      Delete
    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.

      Delete
    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.

      Delete
  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 ...

    ReplyDelete

Post a Comment

Popular posts from this blog

Table Functions, Part 1: Introduction and Exploration

Recommendations for unit testing PL/SQL programs

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