Wednesday, August 3, 2016

The future of Oracle PL/SQL: some thoughts on Sten Vesterli's thoughts

Sten Vesterli published a very thought-provoking post on his blog:

Please stop reading this post, and read that one. When you are done, come on back here for my thoughts on Sten's thoughts.

OK. You read it. Here we go.

First, thanks, Sten, for being such an interesting, wise, sometimes provocative voice in our community.

Next, Sten writes:

Now, on the one hand, I certainly agree that the vast majority of young developers are currently caught up in the modern version of a Gold Rush, which is: "Build an app using JavaScript, pay no attention to that database behind the curtain."

But I can assure you that I still do meet young PL/SQL programmers, regularly, when I am at conferences and doing onsite presentations at companies.

So, young person who writes PL/SQL: do not be afraid! You are not alone! And you are super-smart to have made the choice you did. :-)

Next, Sten offers this advice to managers:

I agree that PL/SQL is a "specialized technology" - it always has been, almost by definition: it is a special-purpose database programming language, and used only within Oracle Database.

But I do think there is another really big "place" in which PL/SQL should be leveraged: 

Any application built on top of Oracle Database

The bottom line is that you cannot build a fully optimized, secure and easy-to-maintain application on Oracle Database without PL/SQL. Doesn't matter if the application is for batch processing, transaction processing, or otherwise.

Sure, you can build an app without PL/SQL. But if you use the database only as a bit bucket, minimizing use of SQL and avoiding PL/SQL all-but-entirely, you will end up with an insecure mess on your hands.

I am not going to go into full "YesPLSQL" mode in this post, though. Instead, I encourage you to check out Bryn Llewellyn's Why Use PL/SQL whitepaper, and his promotion of the "Thick Database" paradigm.

Back to the word "specialized" - I do think that over time we will see a certain narrowing of roles within dev teams. I don't see it as highly likely that JavaScript developers will ever take the time to learn SQL, much less PL/SQL, well enough to avoid botching it up. 

So if I were a fully enlightened manager (feel free to check with my team if you'd like to find out my current status :-) ), I would have one or more specialized people on my team who know Oracle Database inside-out from a developer perspective, and can serve up clean, secure, fast APIs to underlying data and business rules.

Next, Sten offers some advice to developers:

I agree with the first point: it is, unfortunately, impractical to try to convince a developer with no immediate prospects for applying their SQL and PL/SQL knowledge to get up to speed on it.

I don't quite agree with the second point. I suggest that all PL/SQL developers have at least a working familiarity with Java and JavaScript.

Java because you can implement Java classes in the database to complement/extend the reach of PL/SQL. 

JavaScript because you at least need to be aware of what and how many other developers are writing code these days.

Thanks, again, Sten, for this very interesting contribution to an important conversation in our community. 


  1. Hello All,
    This is a very interesting topic, and I am not sure that a general and 100% cutting answer can be given at world level ...

    Here, in Israel, as I followed for several months after the job
    market, I can say that I hardly saw any ORACLE job ... let alone PL/SQL ...

    The IT Managers do invest here an enormous energy in moving away from Oracle at all ... and they are very proud when they are able to do this, regardless of the effective results ...

    For example, my very nice project that I developed for many years based on Oracle Forms, SQL and PL/SQL was thrown away
    in favor of a SalesForce application ... which is far and will still be far from the previous one's features and performance ...
    The next step will be to replace SAP on Oracle with SAP Hana...
    and that will be considered a very high achievement ...

    Each place where Oracle can be thrown away in favor of no matter which other platform, will be considered a success ...
    Just as they could live with Oracle while very few developers
    did indeed master the Oracle internal workings, that is,
    all what is beyond simple syntax that compiles without errors,
    will be a proof that not only the tool knowledge (like PL/SQL)
    is useless ... but the database itself need not be known ...

    I liked Oracle because it always gave me (or, better say, I took to myself) a high degree of freedom in mastering an entire application, without anyone being involved in it in any way,
    not even the Oracle DBA team ...
    But for most others, it is just a data repository and nothing more, applications SHOULD always be coded transparently
    (aka by ignoring) any Oracle-specific knowledge, for making
    Oracle replaceable at any given moment when a manager will
    decide so, at least for preserving his position as a "manager in general", without any focus on any specific technology ...

    When I hear a manager saying "I don't care on which database my application is working"... I think that trying to explain
    to such one how great are PL/SQL, APEX or Oracle in general,
    is like a dialogue with a deaf partner ...

    So, indeed, the very few "traditional" PL/SQL developers
    will gradually retire, while the new ones will probably have never heard about it at all ...

    This is how things look to me in this specific part of the world ... as far as I could see on LinkedIn, in many countries,
    US included, this is much different.

    Best Regards,
    Iudith Mentzel

  2. Oracle has neglected PL/SQL for a number of years (imho). The last release with any significant language improvements was 9.2 (or maybe 10.1). There have been enhancements - optimisations, privileges and SQL interaction - but little to improve PL/SQL itself.

    PL/SQL is such a useful and practical language - easy to write (and read), embedded SQL, free with the database.

    But it can be a very frustrating language to program in. Too many times I find myself writing 'longhand' code, because PL/SQL doesn't provide a suitable solution, instead of writing the functional code.

    If it had more of the bells and whistles (and simple shortcuts) of modern languages then maybe more people could be drawn to it.

  3. Kevan, I hear you and I feel your pain. It is my pain too. Feel free whenever you have a moment to send me your list of top most desired bells and whistles (and simple shortcuts) - steven dot feuerstein at oracle dot com.

  4. We don't hire PL/SQL developers. We hire .NET, Java, and Delphi Developers and expect them to code PL/SQL as part of there responsibility. PL/SQL is so easy to learn and use that we rarely have difficultly with this approach.

    1. Very interesting! I assume you also train them up on SQL? Could you offer any insights as to the approach you've taken to training, what seems to work best, which resources are most helpful?

  5. During the interviews we expect excellent SQL knowledge and usually test on DML Statements with sample schemas. I typically spend a couple of hours on the basics of the syntax and tools with a new hire. Then we let them go, a good developer can switch languages easily and is not type cast into a just a single language. Then we have weekly cross trainings where we cover all sorts of topics and have covered PL/SQL topics many times. Code reviews are also great times to teach, as we can identify easier/better ways to do the same code.

  6. Weekly cross trainings and code reviews? I want to work for YOUR company! :-)

  7. Hello,
    @Robert, if so, then why not go also the other way around,
    namely, hire developers who know SQL & PL/SQL and teach them Java,
    .NET and all the rest ?

    For each developer, the language(s) that he has used for a longer time
    and exercised more seem to be the easiest.
    Though, I would seriously hesitate to hire a Java or Delphi programmer
    if my purpose is to have him perform database backend development ...

    Those are simply but completely different ways of thinking.

    Learning should happen at least "one step before" it is effectively needed in a production project, and NOT on the fly ...

    Best Regards,

  8. Hi ,
    Reading the comments made me think that i should switch to another technology. I have been working in PL/SQL for last 3 years. Sometimes it feels discouraged when people from other technology like java/dot net made the point that there is not much scope for pl/sql anymore. So start learning something else too.
    Looking forward for your suggestions.

  9. Afsar, for a while databases were the "center of the universe" and large numbers of people could make a living specializing only in database technologies. That is less the case today, as the shift to apps (especially mobile) have dominated the software world. But even "back then", many database developers knew other technologies, and there is always a danger to specializing too deeply in one language (I am lucky enough to be an exception in this regard). So you shouldn't be too discouraged by this - or at least not too surprised! There is still lots of activity for PL/SQL developers, especially if you an find a hotbed of Application Express development!

    You have been working with PL/SQL for 3 years - and now? Still doing that? Or are you looking for new opportunities?

  10. "We don't hire PL/SQL developers. We hire .NET, Java, and Delphi Developers and expect them to code PL/SQL as part of there responsibility" In my experience this never works. The average Java or .Net programmers are poor at writing good SQL. Note I said good SQL! Anybody can write SQL but very few understand it. It's deceptively simple. It needs a different mind set. I think the role SQL in enterprise application is always underrated (I am not only talking about Oracle but SQL Server as well). People talk about .NET but not SQL or T-SQL, they talk about Java but not SQL or PL/SQL (and sometime worked in SQL Server as well). I have worked with people (.NET experts) whose approach to fetching data from Oracle database is writing a C# program and access records sequentially!

    I have been working in SQL and PL/SQL for quite a long but I write (occasionally) code in C# and Java as well. Probably I want to learn something about Big data (Spark and Hive) and their integration with Oracle and others.

  11. Hello Steven,

    could you elaborate more on why choosing PL/SQL now is super-smart decision despite no "immediate prospects"? What are long-term prospects then? I worked for 3 years as Oracle Developer and now I'm speeding up with JS and React Native (mobile), but I'm still not fully sure it is good decision. I see Oracle Apex as compelling technology because there is small competition and I see that Oracle tries to make PL/SQL and SQL more popular (Dev Gym, Live SQL, hiring you :) ).Is it only my feeling or actually Oracle will push PL/SQL forward to make it more attractive for developers?


  12. Przemek, regarding:

    "could you elaborate more on why choosing PL/SQL now is super-smart decision despite no "immediate prospects"?"

    You mashed up parts of two different sentences and contexts. "Super-smart" referred to a person who was already successfully leveraging their SQL and PL/SQL skills. Their prospects were, indeed, immediate. They were using the technologies in their jobs right now.

    If you do not have this skill set and do not see any openings (this can vary greatly by geography for example), then yes it is harder to make the argument.

    I do think that Oracle Application Express activity is going to grow steadily and maybe explosively, giving Oracle Database developers a strong path into the future.

    In terms of Oracle making PL/SQL more attractive, I sure hope we are and will be doing this, yes.

  13. I have rarely met (not to say never) a Java or .Net developer that has any idea how Oracle works and how SQL and PL/SQL statements are executed behind the scenes. For example to know what is the difference between a soft parse and a hard parse, what is a latch, how Oracle implements the locking mechanism, what is a context switch, etc. If you don't have this kind of knowledge you will never,ever be able to write SQL and PL/SQL code that scales up in large production environments regardless how many tables you are able to join. And then you will blame the database for the bad performance.

    PL/SQL code written by non-database developers can be recognized from the distance, and not in the good way. But to be honest I don't mind the situation as I get a lot of credit when I am able to optimize poor written PL/SQL batch processes and reduce the execution time from 30 minutes to 30 seconds. And this is usually not because I am that good, but because the code is that bad.

    To get back to the point, I also think the future of PL/SQL doesn't look so bright, not because it's getting replaced by something better, but because it's not a cool technology and many will chose to implement most of the functionalities outside the database.

    My humble opinion is that if you are an IT manager and your application is built on top an Oracle database, an database developer should be a mandatory role in your team. Of course I am being subjective (I really hate the word biased) as I love coding in PL/SQL but remember this: the only way you can write code that performs better that SQL and PL/SQL when it comes to data manipulation is if you write it in C and modify directly the data in database blocks.

  14. Hello Steven,

    I believe there are several problems with PL/SQL that make it not so attractive for new developers either young or not - its lacks:
    1) Lacks of syntax.
    In 2016 we still have to code while loop to get through the collection.
    It doesn't have reference types (even PL/SQL only), so object-relational paradigm is not usable (just imagine you want to execute a method on each object in collection).
    Poor exception handling, this one is a pain for everyone developing enterprise level application.
    Multithreaded, async runs, lambdas. Thinks that are common to other developing languages have no signs of being implemented.
    2) Lack of functionality.
    The set of libraries available grows really slowly. As the programs written on PL/SQL are highly immobile you can find very few projects on Github.
    Lots of functionality can be implemented and improved via standard shipped code-base. Logging, outputting. Even debugging process has difficulties even using the most resent DB version when you operate on collections of records or objects, or pipelined functions.
    The EBR was introduced in 11g. How many applications use it? I don't think there are a lot.
    3) Testing. Have you heard we develop the new version of utPLSQL framewrk with Robert Love? We'll do our best to get the maximum of the language but there things that are simple impossible to overcome. Mocking for example, especially for data, is a very inconvinient topic. With its lack the test coding frequently takes multiple times more time than the tested code writing. Because of difficulties with environment preparation as you can't mock it.

    12.2 release brings only minor enchancements to the language. I believe the language itself can sagnificantly improve if Oracle focuses on not-SQL part of it.

    P.S. Sometimes I dream weather it is time to start developing something like OL/SQL? How cool would it be if the OOP paradigm can envade the thick-DB paradigm without need of ORMs, compiling SQL right near the code. No doubts the logic is likely better expressed in OOP (not data manipulation), maybe Oracle can find a way to borrow that ideas, patterns, designs and push it towards the data as it did with PL/SQL years ago.


  15. Hi All,

    I dont know through what all links but i found myself on this article after 2 hrs of googling.So wanted to take advice from people as you with high knowledge and experience.

    I am working as PL/SQL Developer and Database tuning/query optimization.I have total of 2.5 years experience.Looks like i am stuck with PL/SQL technology. I want to enhance my skills and my pay as well. What should i do to satisfy both. I see people in Java,android switching companies and getting pay as 4 times more than me. If you suggest skills out of oracle, how should i clear the interviews and show my work experience on that skill.

    Please help :)


    1. Ankit, first, why would you be stuck with PL/SQL technology? It's not all that hard to pick up new programming languages. Having said that, though, I have never seen any surveys indicating that an Oracle Database developer (PL/SQL, SQL and related) is paid anything like 4x less than a Java developer. Generally, the pay scales are close and Oracle Database development pays on the high side compared to many others.

      So I am surprised, but I am not denying your experience. I would say, though, that you should take a look at Oracle Application Express ( That gives you a way to leverage your database programming skills directly into website and app development.

    2. Thanks Steven for your prompt reply. I am from India and here the hot skill jumping in is Android and Java where job opportunities and pay is more than of a PL/SQL resource.

      Also, I know basics of java and android but i am not able to crack interviews and without showing that much knowledge they wont take me in these skills (thats y said stuck).

      Just as you said Oracle Apllication Express, are there other skills where i can take on that technology and crack interviews. Thanks for your guidance.

  16. Hi Steve....I am .net developer with exp exp in IT is 6. now i want to learn PL/SQL and switch to PL/SQl developer.I this good idea??

  17. I feel so strange reading all this problems, maybe the difference is that i'm working in homogeneous enviroment with superior role of oracle ? Anyway currently i'm with pl/sql over eight years (or even more). Not having problems with looping over collection and mostly my opinion is close to Ionut S. PL/SQL is mostly not cool because you are not producing any visual representation of your work, PL/SQL can't do this alone (maybe asci pictures). When my directors saying that relational databases will die soon this is pretty much same feeling when they citing one-page articles about PL/SQL ada similarity and basically it is 1970. They saying this year after year "Frameworks are better". But when it comes to details - i'm still here - doing more new things with oracle that they not expected it can be done. If you are programming with PL/SQL from some time you are already familiar with java so this is open natural way. When it comes to cool things and visual representation of your job there are also APEX and Formspider that my boss never expected they exists .There are such of things like ords and rest services jquery and so. If someone will say he is stuck with PL/SQL, my guess is that is a pressure of new trends and low ability to use potential. Summarizing - there are multiple ways that we can slowly prepare when PL/SQL will be gone (it will be about 20 years :-), and even then there will be someone urgently needded).

    1. Jareeq, thanks for contributing your thoughts and experience. Pretty clearly, one's expectation of finding high quality, challenging work developing apps on Oracle Database (SQL, PL/SQL and more) can vary greatly.

      And yes in today's world of "apps in your hand", without question a "backend only" language like PL/SQL is less enticing. Hurray APEX!