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.
Labels:

Comments

  1. iudithNovember 6, 2017 at 8:34 AM

    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. Borys BazhuraNovember 7, 2017 at 1:49 AM

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

    ReplyDelete
    Replies
    1. Steven FeuersteinNovember 7, 2017 at 5:41 AM

      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. ChristianNovember 13, 2017 at 12:59 AM

    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. Steven FeuersteinNovember 13, 2017 at 3:54 AM

      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. ErwinNovember 16, 2017 at 6:15 AM

      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. Steven FeuersteinNovember 16, 2017 at 7:07 AM

      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. iudithNovember 14, 2017 at 7:48 AM

    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

Running out of PGA memory with MULTISET ops? Watch out for DISTINCT!

A PL/SQL team inside Oracle made excellent use of nested tables and MULTISET operators in SQL, blending data in tables with procedurally-generated datasets (nested tables).  All was going well when they hit the dreaded: ORA-04030: out of process memory when trying to allocate 2032 bytes  They asked for my help.  The error occurred on this SELECT: SELECT  *    FROM header_tab trx    WHERE (generated_ntab1 SUBMULTISET OF trx.column_ntab)       AND ((trx.column_ntab MULTISET             EXCEPT DISTINCT generated_ntab2) IS EMPTY) The problem is clearly related to the use of those nested tables. Now, there was clearly sufficient PGA for the nested tables themselves. So the problem was in executing the MULTISET-related functionality. We talked for a bit about dropping the use of nested tables and instead doing everything in SQL, to avoid the PGA error. That would, however require lots of wo...
4 comments
Read more

How to Pick the Limit for BULK COLLECT

This question rolled into my In Box today: In the case of using the LIMIT clause of BULK COLLECT, how do we decide what value to use for the limit? First I give the quick answer, then I provide support for that answer Quick Answer Start with 100. That's the default (and only) setting for cursor FOR loop optimizations. It offers a sweet spot of improved performance over row-by-row and not-too-much PGA memory consumption. Test to see if that's fast enough (likely will be for many cases). If not, try higher values until you reach the performance level you need - and you are not consuming too much PGA memory.  Don't hard-code the limit value: make it a parameter to your subprogram or a constant in a package specification. Don't put anything in the collection you don't need. [from Giulio Dottorini] Remember: each session that runs this code will use that amount of memory. Background When you use BULK COLLECT, you retrieve more than row with each fetch, ...
4 comments
Read more

PL/SQL 101: Three ways to get error message/stack in PL/SQL

The PL/SQL Challenge quiz for 10 September - 16 September 2016 explored the different ways you can obtain the error message / stack in PL/SQL. Note: an error stack is a sequence of multiple error messages that can occur when an exception is propagated and re-raised through several layers of nested blocks. The three ways are: SQLERRM - The original, traditional and (oddly enough) not currently recommended function to get the current error message. Not recommended because the next two options avoid a problem which you are unlikely  to run into: the error stack will be truncated at 512 bytes, and you might lose some error information. DBMS_UTILITY.FORMAT_ERROR_STACK - Returns the error message / stack, and will not truncate your string like SQLERRM will. UTL_CALL_STACK API - Added in Oracle Database 12c, the UTL_CALL_STACK package offers a comprehensive API into the execution call stack, the error stack and the error backtrace.  Note: check out this LiveSQL script if...
5 comments
Read more