Welcome the year of the pig! That maybe appropriate, because I can't help thinking that its 2007 already, and high time that Oracle gave a serious revamp to doddering PL/SQL. Doddering, you say? Well, yes. The past few years have seen incredible language innovation (read Ruby, Python, even JavaScript getting a new AJAXian lease of life) but PL/SQL seems to have been left by the wayside.

I do not know exactly what Oracle have in store for us with 11g, but I sincerely hope it addresses some of my major beefs, which I'd summarise as follows.

1. Give me CLOB-sized VARCHARs
I have a thumping server with a gazillion gigabytes of memory, so why do I spend so much time working around 4000 byte or 32k limits in PL/SQL? Or worse, have my app fail randomly in production when a certain bit of data slips the limit.

These are internal RDBMS implementation details that application programmers should not be concerned with. That's not to say that application programmers shouldn't be concerned about performance, just that they shouldn't be constrained by such arbitrary fundamental restrictions.

OK, so the 4000 byte limit is a SQL thing. But once I am manipulating string data in PL/SQL, if I need a Mb, then please Oracle let me use a Mb.

This is the 21st Century guys. We're not all dealing with simple accounting data. Handling large volumes of text is de rigeur. Text, not nameless objects, be it XML, HTML or just plain ASCII/Unicode.

2. Function-style DBMS_LOB interface
Partly as a result of (1), we often need to resort to DBMS_LOB for dealing with large text. Since it has a largely procedural interface, this usually means we need to drop into PL/SQL when in fact plain SQL would have been preferred.

Rather than deal with temporary LOBs etc, I'd prefer just a function-style interface so most of the LOB handling could be done inline with SQL.

3. Get over the VARCHAR limits with XMLType and XML-related Packages
OK, maybe just a variation on the same theme, but one of the most common situations where application programmers will run into varchar limits is when working with XML. To many of the various XML-related functions and packages are hamstrung by their lack of native support for CLOBs. In many cases, this means what can be elegantly programmed "in the lab" has no practical use because of these limits.

4. Better documentation - proper definitions, real examples
IMHO, most Oracle docs are written according the the "Anne Elk" school of documentation.

It can't get much worse than DBMS_XMLDOM, but let me take an example at random... open the PL/SQL Packages and Types ref and page down a few times. Let's look at DBMS_APPLICATION_INFO.SET_CLIENT_INFO. Parameter is client_info IN VARCHAR2, uhuh. Definition is "Supplies any additional information about the client application". HELLO? Did that actually help anyone who didn't already know what the parameter was for or how it was used?

Normally with most reference guides this is when you turn to the examples to "reverse engineer" the definition. But there are no examples as a rule in the reference docs.

You may find an example in User's Guides, but then again, you may not;) That needs to change.


OK, that's a few for starters. Got any other beefs? Please post a comment, I'd be interested to hear what you have to say.