The Future of PL/SQL : My Opinion

Although a lot of my effort at the moment is focused on DBA features, I have written some articles on PL/SQL enhancements. There are a few neat new features for PL/SQL developers in 12c, but you could be forgiven for thinking it is a little underwhelming. There are two ways to look at this:

  1. OMG. Oracle really don’t care about PL/SQL any more. If they did, there would be loads of new features.
  2. Wow. PL/SQL is so mature and cool that there is really not much more to add.

From a base language perspective, I think option 2 is closer to the mark. PL/SQL is a really stable, fast and mature language. There really isn’t very much that you can’t do with PL/SQL these days. So what is the future of PL/SQL in my opinion?

As part of his role as PL/SQL evangelist, Steven Feuerstein contacted a number of people about their opinions of PL/SQL. When he asked me about my wish list, I suggested all functionality in the Alexandria PL/SQL Utility Library should really be in PL/SQL. A quick look at the Alexandria site shows it includes code that supports a large variety of functionality from a variety of authors. Among other things, this library includes:

  • Generating PDF files.
  • Generating Excel files.
  • Generating RTF files.
  • Microsoft Office Integration (OOXML).
  • Zip and Unzip functionality (separate to UTL_COMPRESS).
  • Parse CSV files.
  • Parse RSS feeds.
  • Generate JSON files.
  • FTP support.
  • Email support (SMTP, POP, IMAP, Exchange).
  • Integration with Google services (Google Maps, Google Calendar, Google Translate).
  • Integration with Amazon Web Services.
  • Integration with PayPal.
  • Integration with Twitter.
  • Consuming and publishing SOAP and REST web services.
  • Logging and debugging APIs and frameworks.

You may see some things in that list that look like duplication of functionality we already have. Oracle 10g introduced UTL_MAIL for sending emails from PL/SQL, but the functionality is so limited, you invariably end up coding your own APIs using UTL_SMTP (like this). Oracle 10g also introduced UTL_DBWS for consuming SOAP web services, but once again, it is often easier to do it yourself directly, or using a simpler SOAP_API based on UTL_HTTP. We don’t even have any reasonable tracing functionality. Instead we have to write our own wrappers for DBMS_OUTPUT, or use someone else’s. So although the PL/SQL language is great, when it comes to integration with other technologies you end up having to do a lot of the heavy listing yourself, or rely on using someone else’s unsupported solution.

So in my opinion, the future for PL/SQL is not in major changes to the language itself, but in bringing these sort of support and integration packages into the database. I think we should avoid forcing overly complex frameworks on people. I’m very much talking about simple utility packages. This could be done in one of two ways:

  1. It is literally baked into PL/SQL. The problem with this approach is you will have to wait for a DB upgrade to get the latest and greatest functionality. That would be a shame since most of the functionality works for multiple DB versions.
  2. Oracle produce their own internal version of the Alexandria PL/SQL Utility Library. So you have an Oracle supplied, supported and maintained library of functionality you can download and use, independent of database version. This means updates are independent of major database version changes.

I think option 2 would make a lot of sense. If you think about it, we almost have a precedent for this in the form of APEX.

  • APEX is built on PL/SQL.
  • It ships separately to the main database releases.
  • Each release supports a variety of DB versions.
  • It brings with it a bunch of utility packages. They are there to support APEX, but there is nothing to stop you using them for your own applications, like this example of using APEX_JSON.

Imagine how exciting it would be if part of the Oracle 12cR2 or Oracle 13c announcement included a huge library of support packages like this! 🙂

Cheers

Tim…

Author: Tim...

DBA, Developer, Author, Trainer.

3 thoughts on “The Future of PL/SQL : My Opinion”

  1. Now, THERE is something I would gladly consider if it became a chargeable extra!
    Instead of the heaps of redundant and never-asked-for “features” that invaded the separately-priced list nowadays….

  2. Fantastic ideas, Tim. I have just one follow up question for you and others: what would you think of harnessing our worldwide community of PL/SQL and SQL experts (some inside Oracle, most not) to build those libraries as open source code bases?

  3. Steven:

    I am a fan of OSS, but I’m not sure I think it is the right way to go in this case.

    I have mixed feelings about it being community driven. I think there will be a tendency for people to push their own standards or fork code if they don’t get their own way. I do not want to see the sort of thing we see in some open source communities, where there are loads of forks of the same code base, which results in lots of wasted effort and nobody really knowing what is “the best” fork to use.

    Let’s look at the tracing thing for example. Off the top of my head we have Logger, Log4Plsql, P (from you), DEGUG (used by Tom) and DSP (that I wrote). Which of these gets put in “the library”? Do they all get put in? See what I mean?

    I think the power of this will be if this functionality is considered to be “built-in” by most users. If it ends up being a rambling app store of varying quality and documentation, I can see people ignoring it.

    What’s more, I want Oracle to use this stuff internally. Some of our existing APIs feel like Oracle built them and left them for dead. If they were being used internally, I’m sure they would have developed somewhat. If this is a 3rd party app store I can’t see Oracle using any of it internally…

    If you look at a number of the things listed, Oracle must already have a solution they use. APEX contains a lot of this functionality already. I doesn’t feel like it would be much work for Oracle to take ownership of this in a coordinated fashion. Of course, as it is zero profit, finding bodies will no doubt be a problem. 🙂

    Cheers

    Tim…

Comments are closed.