PL/SQL New Features in Oracle Database 12c


I recently put some more PL/SQL new features articles live.

I’ve also posted a top-level new features article.

This contains a number smaller features as well as links to other articles on the site that discuss some of the new features in greater depth.

I’ve got a couple of PL/SQL books I’ve got to read and review, but I’ve been holding back because I wanted to get my take on this subject written before I was influenced by others. I guess I don’t have that excuse any more. :)



The Future of PL/SQL : The People Respond


I put out a post yesterday called The Future of PL/SQL : My Opinion.

For an Oracle-related blog, putting something out at the weekend is a sure fire way to get nobody reading it. If I look at my website, the hit rate at the weekend is about 1/5 of that of a week day. By the time most people get back to work on Monday they have a stack of blog posts to read and yours will probably fall into the “mark as read” pit of their RSS reader. :) In a similar fashion, people’s timelines on social media are generally so crowded, your “look what I’ve just written” tweet will probably be lost amongst the talk of alcohol, bad food and photos of the kids…

So having said that, I chose a Sunday to put out my manifesto for the future of PL/SQL and was surprised by the volume of feedback. I know there are a lot of PL/SQL programmers out there, but it’s easy overlook that when many of the other programming communities are so much more vocal in comparison. It’s good to know there are so many people out there who still care about PL/SQL! You’ve made an old man happy. :)



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! :)



PL/SQL White Lists in Oracle Database 12c Release 1 (12.1)


I’ve been playing about with the ACCESSIBLE BY clause to create PL/SQL white lists in Oracle 12c. Here’s the article I wrote about it.

There seem to be some discrepancies in the documentation*, which I’ve highlighted in the article. Not sure if they are documentation errors, functionality that has been pulled and will reappear in 12cR2, or just misunderstandings on my part. :)



* I’ve posted comments on the docs, so if they are documentation errors they may get fixed.

WITH Clause Enhancements in Oracle 12c…


After doing a number of 12c installations, I decided it was time to write something new. I figured I’d pick something easy to start off with, so here is the first thing off the press.

Over the last year I’ve heard a few speakers talk about these enhancements, but I got myself into a right pickle while I was working through this. If you caught the first draft of this article you would see I made a complete mess of it and jumped to all the wrong conclusions. Having worked through it again with fresh eyes, it all became clear. Of course, if you didn’t see the first draft, then ignore what I said. The article has always been perfect. :)

I think I will carry on doing some of the simple stuff to build my confidence before I can tackle something big, like pluggable databases. :)



Update: Note to self. Searching the 11gR2 docs for “PRAGMA UDF” is unlikely to result in you finding this 12c feature. Who’da thunk it? Thanks to Tom Kyte for pointing me to the correct doc reference. I’ve altered the searches on my homepage to default to the 12c docs, so hopefully I won’t be such a Muppet next time. :)

Why is it easier to get things to work at home?


I’ve been grabbing a few minutes here and there over the last couple of work days trying to get something working with no joy. I this case it is a DAD entry in the “dads.conf” file on the OHS running as part of 11gR2 Forms & Reports Services on WebLogic 11g (10.3.6). I started to think that maybe this functionality is disabled on the installation or something like that, but couldn’t find any reason for that to be the case.

In a fit of desperation I came home tonight, fired up a DB and a F&R installation, defined a DAD and it worked first time. Aaaarrrggghhhh!!!!!

On the plus side, I know there is nothing fundamentally weird about the OHS installation that comes with 11gR2 F&R Services. On the down side, I still have no flippin’ clue why it is not working on the installation at work. Think I need to get in early tomorrow and focus on it for a little while. No doubt it will be something stupid I’ve missed while trying to do a bunch of different things at once.

File this one under:

  • #DoingLotsOfStuffBadly
  • #WeDoNotDoMultitasking
  • #MakingSimpleStuffHard



Update: Fixed it. It was the bloody firewall. :) #DontForgetToCheckTheFirewall

Advert: PL/SQL Masterclass Updates…


This month I’ll be doing some PL/SQL Masterclasses for Oracle University. The following dates have been confirmed:

  • Singapore (14-15)
  • Syndey (18-19)
  • Melbourne (21-22)

For more details contact Oracle University here.

Originally there was an extra session in Hong Kong, but this has now been cancelled.

In November the following dates are scheduled:

  • Bulgaria (22-23)
  • Serbia (25-26)



Advert: Asia Pacific PL/SQL Masterclasses…


It’s a little over a month before I start a run of four PL/SQL Masterclasses in the Asia Pacific region.

  • Singapore (14-15 October)
  • Syndey (18-19 October)
  • Melbourne (21-22 October)
  • Hong Kong (25-26 October)

If you are interested, contact Oracle and get booked on. The details are here: APAC Seminars.



Oracle Database 11g: Advanced PL/SQL (1Z0-146)…


Lewis Cunningham recently mentioned on twitter that he had taken this exam and it reminded me I had been meaning to take it ever since it was announced in beta, but never actually got round to it. A quick search of my blog revealed it’s nearly 2 years since I mentioned I wanted to sit this exam (here).

So yesterday I sat and passed the exam and here are some thoughts on it (without mentioning specifics about the questions)…

This is the first time I’ve sat an OCP exam done by Pearson VUE and I was pleasantly surprised. The test interface is a little cleaner and more modern looking that previous exams I’ve taken. There was one minor glitch in that every time there were two exhibits they were shown in the wrong order, such that when the question asked you to look at Exhibit 1, the content it was referring to was in Exhibit 2 and vice versa. For most questions this was obvious, but there was one that if you followed the instructions to the letter would have made the question impossible to answer correctly. Of course, if you had seen the glitch on the other questions it wasn’t a leap to assume the same problem was present on this question also.

The SQL and PL/SQL exams use lots of exhibits and some can be quite big. In a number of cases the questions can be answered without referral to the exhibits if you know your stuff because the incorrect answers reference “facts” about PL/SQL that are blatantly not true. Even when you do have to refer to the exhibits, I would suggest you treat it like you are debugging code and read the question and possible answers before reading the exhibit. Why? You don’t start debugging code before you know what the errors are. In the same way, if you know the possible answers you can ignore the majority of code in the exhibit and focus on the area of interest. Reading the whole exhibit would take ages, so be efficient about it. Ignore the fact the question tells you to look at the exhibits first and you will answer the questions much faster.

The exam is very much 11.1 focussed, so there are a few questions where the “correct” answer is somewhat dubious if you have been using 11.2 for a while. Keep in mind the 11.1 state of play and don’t try and be too clever. That said, for the most part the 11.1/11.2 issue is irrelevant.

The context of a question is a very important thing and one that I think the OCP questions sometimes lack. As an example, one question asks you which of the 4 answers can be produced by a specific built in package. The correct answer as far as I’m concerned is all four, but it is rare that questions need all answers to be checked, so this is where you need to put on your PL/SQL hat. As a direct call from PL/SQL only two answers are correct, but if you call the API via a query all four answers are possible. So you have to make a judgement call as to what context the examiners are using. I figured, as it’s a PL/SQL exam, I should ignore SQL and that turned out to be the correct move.

There were only two questions that I didn’t know the complete answer to directly, but in both cases, using a process of elimination I was pretty confident I had a reasonable shot at the correct answer. Even if you don’t know every fact relating to a subject you can usually elimitate some of the answers, which will up your odds. Don’t panic.

The exam is scheduled for 90 mins and I was done in a little over 30 mins. The examiner gave me a bit of a look when I came out that early, and another when he saw my 100% score. I just smiled and told him I’d been doing PL/SQL for 16 years and had been teaching the contents of this exam for over 2 years. It would have been a little tragic if I hadn’t come close to 100% really…

So I guess in the next few days I’ll recieve my “Oracle Advanced PL/SQL Developer Certified Professional” bumf. I quite fancy doing something else now. I obviously spent too many years in education and I need an exam fix every so often. :)