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:
- OMG. Oracle really don’t care about PL/SQL any more. If they did, there would be loads of new features.
- 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:
- 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.
- 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! 🙂