2

All,

When writing SELECT queries for Oracle 11i databases, why do some tables in Oracle enforce applying quotation marks to the fields being selected, and others not.

An example I've recently come across:

In Aqua Data Studio in a Query Analyzer window I have attempted to select the same field from two different tables:

    select _id from table1
    select _id from table2

table1 and table2 differ greatly, but Only table1 executes this select statement without an error. When I try to execute this statement for table2 I get the following:

ORA-00904: "_ID": invalid identifier
Script line 1, statement line 1, column 7

However, when I execute the second statement like this it works perfectly:

    select "_id" from table2

Does anyone know what is going on here, why is this the case, and what could the key differences be between the tables be that is causing this?

Thanks

Danzomida
  • 475
  • 2
  • 5
  • 11
  • 1
    Please see my answer to another question: http://stackoverflow.com/a/6030439/146325 – APC Jul 04 '13 at 13:49

2 Answers2

4

The answers and links about casing are correct, but your situation goes a little beyond a simple case issue, both because your column name started with an underscore and because your client is apaprently usually hiding the quoting from you.

If you tried to create a table with a column called _id, without quoting it, then you'd get an 'ORA-00911: invalid character' error, the cause text of which says 'identifiers may not start with any ASCII character other than letters and numbers'; which is actually wrong as well since it can't start with a number either (for example, 0_id gives 'ORA-00904: : invalid identifier'). This is backed up by the database object naming rules:

Nonquoted identifiers must begin with an alphabetic character from your database character set. Quoted identifiers can begin with any character.

So it looks like Aqua Data Studio is following a convention of enclosing the upper-case version of the object name you supply in double-quotes, a practice mentioned in one of the linked posts.

From what you've shown, select _id from ... gets passed to Oracle as select "_ID" from ..., which is fine if the column name was created as "_ID". It appears that is the case for table1, but table2 was created as "_id" - so that case mismatch generates the legitimate ORA-00904 you're seeing.

Your client is not modifying a column name that is already enclosed in double-quotes, so select "_id" from ... is passed through to Oracle as-is, and works OK for table2 (but, conversely, would fail for table1).

Oracle requires the name to be enclosed in double quotes if it doesn't follow the rules for unquoted identifiers, and if it was created as quoted - unless the original quoted value was valid anyway, i.e. follows the unquoted rules and was entered in uppercase. Since your column name starts with an underscore, as far as Oracle is concerned all references to it have to be enclosed in double-quotes regardless of the case. Your client is just doing that in the background if you haven't quoted it yourself.

Following the advice others have given to avoid quoted identifiers and to always use names that are valid unquoted would avoid issues like this.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
3

The problem comes from the creation of the object.

If you create an object with lower case AND quotation marks, it will enforce case sensitivity.

So you'll need to use quotation marks and right casing to use it.

If you create without quotes (or all in upper case), you won't face any "case sensitivity" problems, and will be able to select object with lower or upper case (without quotation marks)

Raphaël Althaus
  • 59,727
  • 6
  • 96
  • 122