Thursday, 13 September 2007

What features would you like to see in PL/SQL?

Every day I read posts that start "I was going to blog about X but Y beat me to it". It looks like it's my turn. I've been sitting on a post for improvements I'd like to see to PL/SQL, and it turns out Steven Feuerstein has set up a site to capture PL/SQL improvements we'd all like to see. Dag-nab-it!

Anyway, the gist of my original post follows, slightly rewritten to make it look like I didn't think of the idea first ;)

With the arrival of Oracle 11g we see a new set of features for PL/SQL. PL/SQL from my point of view has always been the ideal procedural language for the Oracle database, as many years ago I programmed with Ada which was the basis for PL/SQL syntax so I didn't have to learn a new language from scratch, I find the syntax very easy and clear to work with, and a breeze to teach (compared to Java for instance).

From experience, my positive outlook on PL/SQL is shared by a number of Oracle experts out there, with the main comment being "PL/SQL does what it needs to do." In other words it's a very mature language.

This doesn't mean that we shouldn't drive for further innovation in the PL/SQL language though, as there are many features in other languages that could make our PL/SQL programming experience more rewarding. If you look at the Java camp, committees upon committees, and blog entries upon blog entries are dedicated to improvements in the language for the next version. I can't say I've seen the same for PL/SQL, so Steve's initiative here is a good one.

So what improvements would you like to see in PL/SQL?

To start ball rolling, I'll suggest a couple and will post onto Steve's site:

1) I miss the ternary operator from other languages such as Java. The ternary operator takes the form:

(<boolean>) ? <return true expr> : <return false expr>;

So for a trivial example from Java:

boolean b = (1 < 2) ? true : false;

The ternary operator is really a short hand CASE or IF-ELSE-END IF statement. The power of the ternary operator is it supports expressions within each component, and expressions within expressions and so on. However the ternary operator is also responsible for some evil looking code because of this ability, so definitely it has its pros and cons.

2) To retrieve a sequence number in PL/SQL, you need to wrap this in an implicit SELECT-INTO statement or similar. What I really want to do is the following short-hand:

v_number := my_seq.nextval;

(and thanks to Justin Cave's response to my question on OTN, turns out this is available in 11g, as published in this AskTom article under the section "It's the little things". Neat!)

3) Assertions are a common feature in many languages beyond simple exception handling. They allow you to test a condition, and if it fails, stop the program. They are ideal for debugging programs because you can (for example) place the assertions at the beginning of each PL/SQL module to ensure incoming parameters are not null.

So what features would you like to see in PL/SQL? Why not head over to Steve Feuerstein's site and record your thoughts.


Noons said...

none of the links work.
how web 2 is that?

Chris Muir said...

Fixed, though you'll need to navigate to the Home page link once you select the link to Steve's site. I can't seem to find the direct URL to the home page.



Patrick Wolf said...



PS: What a coincident, we blogged about the same today :-)

Chris Muir said...

Thanks Patrick. I'm guessing a little insider knowledge about APEX URLs needed there?

I think I discovered the site originally from Alex Nuijten's blog post here, though the array of bookmarks I have at the moment isn't so useful in tracking back the original source.

If anybody is interested in Patrick's post, it can be found here.

Happy reading!


John Scott said...

>Thanks Patrick. I'm guessing a little insider
>knowledge about APEX URLs needed there?

Actually, it's not inside knowledge about APEX URL's ;)

People started to blog about it before we 'officially' announced it (and released a 'nice' URL for it).

Ahh's difficult to put that cat back in the bag now ;)


Chris Muir said...

Ah, well in that case good luck with the new site, and well done to the Apex lads for putting this together. I certainly hope we see some community discussion on good ol' faithful PL/SQL.


Anonymous said...

Ad assertions: probably give the existing package DBMS_ASSERT a trial
Best regards,

Chris Muir said...

Hi Martin

My understanding is the dbms_assert package is for checking user input, especially useful for guarding against SQL injection. I don't think it provides the functionality I'm looking for. Can you clarify how you think the dbms_assert package would be the same as the Java assert function?


John Scott said...

Hi Chris,

Just as a follow-up, the correct URL that people should use to access that site is -



Chris Muir said...

Thanks John, I've updated the original URLs.


Adrian said...

Hi Chris

I would really like an equivalent of the continue statement in C.

The continue statement passes control to the next iteration of the nearest loop statement in which it appears, bypassing any remaining statements in the loop body.


Chris Muir said...

Hi Adrian

It looks like the 11g fairies have granted your request! Check out the continue statement in the pl/sql manual for 11g.



Steven Feuerstein said...

Hey Chris,

Thanks for posting about I Love PL/SQL And.

And thanks for submitting some suggestions. I am working through the submissions now and will post a number. For future reference: your submission is MUCH more likely to get published if you fill out all the fields and not simply reference a URL.

SOMEONE has to complete a "pet peeve" and if it's me, well, I am kind of busy.

Now, having said that, the idea of an assertion function is a good one and I will publish it. In the meantime, anyone interested in such a feature, you can install my assert.pkg package, which is available as part of my "".

This zip contains all the helper code for my seminars and can be downloaded at:

and then click on the "Trainings, Seminars..." link.

Regards, SF