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.
Thanks in advance for your input!
Steven
11 comments:
After adding the complete error message to the sql%bulk_exceptions array, my next wish is that insert..select would support returning into.
Regards,
Rob.
Support for GOTO
I am happy (?) to inform you that PL/SQL already does support GOTO.
But of course it should only be used as a last resort (or if you have made an inexcusably deliberate decision to be lazy).
Rob, that's a good one, all right insert...select with returning.
I'd also like to be able to specify the column to be used as the index for BULK COLLECT INTO. That would be, as my son used to say, sweet.
I definitely second both Robs suggestion for "insert.select returning" and specifying index-column for bulk collects.
Especially the latter is one I would use on an almost daily basis.
As for GOTO: I suggest it be removed in a future release. ;-)
Hello Steven,
I'd be very happy to specify a column as index key in bulk collects for the so-called "associative arrays", something like:
select colA, colB, colC
bulk collect into collA, collB, collC
using colX, colX, colY as key
from some_table...
Bye!
Flavio
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;
another thing is bind variables by name (and not only by position).
for :
execute immediate 'update t set a = :a , b = :a , c = :c ' using a,a,c;
i want to bind by name and use a state a variable only once :
execute immediate 'update t set a = :a , b = :a, c = :c' using a,c;
that's it.
haki.
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.
Haki,
I should have answered both at once. You also ask to "use variable from a table of records in the where and insert\update clause of a forall statement."
This is supported in Oracle Database 11g.
SF
thanks Steven. i'll make sure to check that on 11G.
about the binding, i meant something like &&1 in SQL*Plus.
Haki.
I'm coming late, but here it goes, anyway:
- Enumerations (e.g. TYPE MSG_STATE IS (DRAFT, READY, WAITING, SENT);) as already seen in package STANDARD for BOOLEAN
- 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.
Another hit for my wish list:
DECLARE
l_record some_table%rowtype;
BEGIN
UPDATE some_table
...
RETURNING RECORD INTO l_record;
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 :-)
Post a Comment