Wednesday, August 13, 2008

Larry Ellison, please join the 21st century!

I've sniped at Microsoft at least once, so in the interest of balance I'll take a crack at the other software giant I rely on but also frequently complain about: Oracle.

Oracle is truly amazing. Now, I don't have much experience with other relational database systems, so this isn't comparative. But relational databases are amazing. I give it a query of what I want and if I cross all my t's and dot all my i's, then huge databases are searched rapidly (often a matter of seconds).

My first complaint is with inconsistency in syntax. Oracle has several flavors of text types depending on how big you might let your text get. I mostly query databases, not create them, and so I generally want to treat them all the same. Now there might be some good reason I need to use a different function to get the substring from each type, but I really don't want that hassle. But if I'm stuck with it, why couldn't you keep the argument orders the same? Standard substring, like every substring method I've ever met, has the order: string, start, length. But for the really big text columns ("CLOB"s), it's string, length, start. WHY???

But worse, is when I'm having trouble dotting those i's and crossing the t's, Oracle really doesn't give much help. The error messages are somewhere out of the 60's.

For example, one handy feature of Perl (and other environments) is some attempt to identify common pitfalls and give hints about them in the error messages. A common mistake for me is to include an extra , in my query

select x,y,z,
from mytable

In this example, of course, it's small -- but many of my queries are 30-40 lines long. Surely it could detect that the unrecognized field name is a reserved word and therefore hint that I've included an extra comma.

Another example. For one query I have I've been parsing out a numeric string and then trying to convert it to a number. Alas, somehow my parse is failing and I'm getting some unconvertable strings back. Oracle gives me an error that it can't convert something to a number -- but keeps that something a secret from me!

I could go on-and-on. Line numbers for the error are frequently non-helpful, the error messages don't give the context of the offending bit, etc, etc.

The one thing I haven't tried is to edit my queries in Visual Studio, which has an SQL mode. I really should try that -- not that VS's error messages are always golden, but it is good about highlighting the likely neighborhood of mistakes in a way SQL Developer (the Oracle interface I use) just doesn't even attempt

Ah well, I'll live. Larry probably has bigger fish to fry. Personally, though, if it was my software I'd be cringing.

1 comment:

Steve Marshall said...

Hi Keith

If you use tools like TOAD or SQL Developer there are command line completion tools that provide you with the parameter info needed... which does help. But I do agree that some of the error messages are a little cryptic...