Friday, October 28, 2011

Wanted: Your Ideas for Enhancements to PL/SQL

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?

Feel free to reply to this post 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.

i agree about setting the index values in associative array using bulk collect.two other things, i would love it if could use variable from a table of records in the where and insert\update clause of a forrall statement. somtheing like : forall i in arr.first .. arr.last update t set a = arr(i).a where b = arr(i).b;

Haki, Regarding "i want to bind by name and use a state a variable only once", you can do this now simply by placing the DML statement inside a BEGIN-END block. Binding of dynamic PL/SQL is done by name, not position.

- Scoped objects (i.e., I want to create an object type in a PL/SQL block, without storing it in a schema - and without SQL limitations such as "no booleans" and "no index-by tables"). I had to switch back and forth several times between objects and records as I discovered the limitations of each type.

- The ability to call functions that return or receive boolean parameters from SQL, JDBC, etc. (not only from PL/SQL)

- Finally for exceptions

- Exceptions with parameters (so we can pass some extra info to the handler), as in C++, Java, C#, Python and many other languages (without jumping thru hoops with pragma exception_init, and raise_application_error number codes)[[What about this?DECLARE MY_EXC EXCEPTION(-20001);BEGIN RAISE_APPLICATION_ERROR(MY_EXC, 'Message); END;]]

- The ability to easily declare recursive types (e.g. OBJ_T is a type that contains a number, a string and an varray/table of other OBJ_Ts)

- Variable number of parameters in procedures and functions

- (this applies more to SQL than PL/SQL) Small adjustments to have more standard compliance: AS keywords as in "select x from table1 AS tbl"; SUBSTRING, OCTET_LENGTH functions; VARCHAR where '' is different from NULL (e.g. length('') returns 0 vs. length(NULL) returns NULL) and other datatypes; MINUS vs. EXCEPT DISTINCT; etc.

A concise way of returning the full record into a record variable without having to specify the whole column list, pretty much like the FETCH statement and support for collections an other statements other than UPDATE is welcome :-)