Custom views and Oracle: The tyranny of capitalization

  February 09, 2009

Oracle has a funny way of dealing with capital and lowercase letters in things like tables and views.p But then, Oracle is funny about a lot of things.

For example, did you know Oracle still has a 30-character limit for all object names?p No table with 31 characters.p Or that Oracle doesn't have a function for getting the difference between two dates in, for example, seconds, but it does have a function returning the difference as a floating-point number of months.

But a "month" isn't a single time unit, you cry.p Yes, well, it's a 30-day month.

Huh?

Oh well, their buildings look like the database flowchart symbol and they have pretty, expensive lakes, so that's something I guess.

Oracle Buildings

Anyway, back to case-sensitivity.

Our tables and views are (automatically) created using all-lowercase letters.p That means you can use any letter-casing you want when you access them.p Some report-writing tools automatically upper-case things, but that's OK.

With our views, however, there's a little snag.p The columns on the custom-field views (reviewcustom and defectcustom) are reproduced exactly as you typed the field title in the custom-field admin screen.p This was supposed to be a good thing because it's very easy to read.

Well if you're like 100% of our customers, you probably used mixed-case titles, because forms should say "Severity:" and not "severity:".p But that means mixed-case column names.

When you use mixed-case names with Oracle, you lose the ability to reference those names with any old letter-casing you want; now you must match the names exactly.

So that means this query works:

SELECT Severity, COUNT(*) FROM reviewcustom GROUP BY Severity

But this one doesn't:

SELECT severity, COUNT(*) FROM reviewcustom GROUP BY severity

And this one doesn't:

SELECT SEVERITY, COUNT(*) FROM reviewcustom GROUP BY SEVERITY

We're going to fix this in a future release.p For now, you'll just have to make sure you get the column names exactly right.