PL/SQL Formatting : More pearls of wisdom from Bryn


glasses-272399_1280-smallAnother topic of conversation that came out of Bryn‘s session at Oracle Midlands related to PL/SQL code formatting. I’m not sure I agree 100% with his opinion, but it’s certainly making me think about my current stance.

When I said I was going to blog about a couple of the points he raised, Bryn emailed me some stuff, which I’m going to use here. So this is Bryn’s content, with some of my opinions thrown in. I will no doubt get told off for not doing the subject justice, like I did with the last post. :)

Bryn: Look at page 1 (below). This makes PL/SQL look so old-fashioned. I’m sure that this is part of what makes it unappealing to the modern youth. How easy do you find it to read its real meaning?

Me: With a few minor formatting changes, this is pretty much what I do. :) As a result, it looks really normal to me and I like it, but if I try to put myself into the head-space of a young person, it does look overly formal, and as Bryn said in his talk, we are essentially shouting all the keywords and using naming conventions that detract from the true meaning we are trying to convey. I don’t like to admit it to myself, but I kind-of see his point. It does look a little Pipe and Slippers.

Bryn: Now look at Page 4 (below). Identifiers are like proper nouns in English prose. They’re capitalized to let them stand out. The capitals in Object_ID (or DBMS_Output) are an aid to pronunciation.


Me: This is a slightly unfair comparison, since there is no syntax highlighting, which is on by default in all PL/SQL IDEs, but it does look less “old”. I understand Bryn’s point about the capitals potentially aiding pronunciation, but if I’m going to stop “shouting” the keywords, I actually prefer the look of it all in lower case myself. :) I agree that identifiers still need “_” between words. Although camelCase is used by many languages, it doesn’t work so well in Oracle. It looks fine in your scripts, but when you are looking at dictionary views, much of the information is presented in uppercase, so “uniquenessContraintMissing” becomes “UNIQUENESSCONSTRAINTMISSING”, which sucks. The prefix/suffix used on identifiers can be confusing, especially since an alternative was mentioned in the previous post.

Bryn: As promised, here’s my-marked up copy of the Gettysburg Address. It’s considered to be an exemplar of English prose, so having it mangled into an incomprehensible mess by the robotic application of “code formatting best practice” makes my point very vividly. … Feel free to use it. But do make sure that you credit the author, President Abraham Lincoln, and me for having had the idea to use it to make fun of the anally retentive code formatters who jointly conspire to make PL/SQL look so dated.



Me: Yeah. Point made! :)

I’ve tried to alter my code formatting in the past, which invariably meant I had to go back and reformat it a couple of days later when I couldn’t stand knowing it wasn’t “how I usually do things”. :) Having said that, if you read the last blog post, you would see I tried to stop shouting. :) It’s not what Bryn likes, but I think it’s a step in the right direction… Maybe…

Am I going to redo all my articles, blog posts and videos? No. Am I going to try and change? At the moment I’m thinking yes, but I reserve the right to do a u-turn at any point.

Don’t forget to check out these whitepapers from Bryn.



Preventing PL/SQL name clashes. You learn something new every day!


glasses-272399_1280-smallI mentioned in yesterday’s Oracle Midlands post, Bryn had an example of some syntax I had not seen in 20+ years of PL/SQL development.

You tend to name PL/SQL parameters and variables in such a way as to prevent name clashes with table columns, but you could just qualify each use of a PL/SQL identifier with the identifier for the name of the block in which the item is declared in this case the procedure name. In the following example, “dummy” is both a parameter name and a table column, but Oracle knows exactly what you mean because you’ve fully qualified it.

create or replace procedure my_proc(dummy varchar2)
  amount pls_integer;
  select count(*)
  into   my_proc.amount
  from   dual a
  where  a.dummy = my_proc.dummy;
  dbms_output.put_line('amount=' || my_proc.amount);
end my_proc;

set serveroutput on
exec my_proc('Y');

PL/SQL procedure successfully completed.


You can also do this for parameterised cursors and named/labelled blocks. You learn something new every day! :)

This was just one of the points Bryn discussed in his whitepaper called Doing SQL from PL/SQL: Best and Worst Practices.

Bryn: Look for this section: “Name capture, fine grained dependency tracking, and defensive programming”. This discussion leads to this:

Principle 1: In embedded SQL, dot-qualify each column name with the from list item alias. Dot-qualify each PL/SQL identifier with the name of the name of the block that declares it.

It is also mentioned in his paper Why use PL/SQL?



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)