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.

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

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 p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel