Custom views and Oracle: The tyranny of capitalization
Develop | Posted 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.

Close

By submitting this form, you agree to our
Terms of Use and Privacy Policy

Thanks for Subscribing

Keep an eye on your inbox for more great content.

Continue Reading

Add a little SmartBear to your life

Stay on top of your Software game with the latest developer tips, best practices and news, delivered straight to your inbox