28 October 2011

Wanted: Your Ideas for Key PL/SQL Enhancements

On November 9, I will be doing the keynote presentation at the 100th member meeting of the Northern California OUG. The day before that I will visit with the PL/SQL development team at Oracle HQ.

It's always great to catch up not just with Bryn Llewellyn, the PL/SQL Product Manager, but also some of the developers themselves (those very special human beings who actually build the programming language at the center of so many of our lives). They'll interrogate me to get a sense of what developers are doing (and not doing) with PL/SQL out there in the "real world." And I'll find out whatever I can about new features in the upcoming release of the language (in this case, 12.1).

I usually take advantage of this wonderful opportunity to also tell them about what I'd love to see added to (or fixed in) PL/SQL. Of course, my ideas are limited to my own experience. So I thought I would ask all of you for your ideas.

What changes in PL/SQL would make the biggest difference for you and your applications? 

You can reply to this newsletter/blog with your thoughts. You can also visit ILovePLSQLAnd.net to vote on a set of enhancement ideas, and even submit your own for consideration.

I'll pull together all the ideas I receive and present them to the PL/SQL team. But I must warn you: I don't expect to come out of this meeting with a list of confirmed enhancements planned for future PL/SQLs. That simply isn't the way Oracle plays the game. Instead, you'll just have to hold your breath until some future version of Oracle Database delivers the enhancement you requested.

Thanks in advance for your input!


  1. Hello Steven,

    I have already submittet a few topics to your ILovePLSQLAnd.net site.

    In my eyes the most important topics are:

    - BULK COLLECT non-sequentially into collections
    - Get COUNT of elements in subset of collection

    - Show package's subprogram name in call stack and backtrace (which shouldn't be so difficult if PLSCOPE_SETTINGS is set to "IDENTIFIERS:ALL")

    And nice to have would be:
    - Pass exceptions as arguments
    - Support definition of private attributes and methods in Object Types
    - Better Access to New/Old Data In Triggers

    Greetings, Niels

  2. Hello Steven, All,

    Just to continue what Niels already has written above, a natural request that I have already seen in other places as well is to add access in the DML triggers to :OLD.ROW and :NEW.ROW record variables defined implicitly as having type MyTable%ROWTYPE, thus allowing them to be passed as one single actual parameter to other subprograms.

    Once the "SET ROW = ..." update syntax and the INSERT of entire records became supported,
    adding the :OLD.ROW and :NEW.ROW records is just the natural counterpart of these.

    Thanks & Best Regards,
    Iudith Mentzel

  3. Another issue that would be very useful is having the possibility to specify the starting row when using BULK COLLECT INTO (without the need to use "Row_Number() OVER ()" or primary-key-filtering to do it manually).

    This would especially be very useful for web-programming, for example if you want to display a maximum of 50 records starting from row 1001 in a table you should be able to easily say:

    SELECT fields,...
    STARTING AT 1001 LIMIT 50;

    Greetings, Niels