61

I thought that the quotation mark (") was simply a type of grouping marker but I'm debugging some NHibernate code and notice that while

SELECT * FROM site WHERE site_id = 3;

Works fine

SELECT * FROM "site" WHERE site_id = 3;

fails with a table or view does not exist error.

What gives?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
George Mauer
  • 117,483
  • 131
  • 382
  • 612

2 Answers2

112

Putting double-quotes around an identifier in Oracle causes Oracle to treat the identifier as case sensitive rather than using the default of case-insensitivity. If you create a table (or a column) with double-quotes around the name, you must always refer to the identifier with double quotes and by correctly specifying the case (with the exception of all upper case identifiers, where double-quotes are meaningless).

Under the covers, Oracle is always doing case-sensitive identifier matching. But it always casts identifiers that are not double-quoted to upper case before doing the matching. If you put double-quotes around an identifier, Oracle skips the casting to upper case.

So if you do something like

CREATE TABLE my_table( 
  col1 number,
  col2 number
)

you can

SELECT * FROM my_table
SELECT * FROM MY_TABLE
SELECT * FROM My_Table
SELECT * FROM "MY_TABLE"

but something like

SELECT * FROM "my_table"

will fail.

On the other hand, if you do something like

CREATE TABLE "my_other_table"( 
  col1 number,
  col2 number
)

you cannot do

SELECT * FROM my_other_table
SELECT * FROM MY_OTHER_TABLE
SELECT * FROM My_Other_Table
SELECT * FROM "MY_OTHER_TABLE"

but this

SELECT * FROM "my_other_table"

will work

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • 4
    This answer leaves out the fact that it also allows identifiers to contain characters that would otherwise be illegal, such as a space character. It's also probably best practice to use only upper case letters, digits, and underscore. Doing so will make everyone's lives easier. – jpmc26 Jun 14 '16 at 18:55
  • The answer also leaves out the fact that it also allows identifiers to start with a non-alphabetic character (i.e. numeric). The full list can be found in [Oracle's documentation](https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/Database-Object-Names-and-Qualifiers.html#GUID-3C59E44A-5140-4BCA-B9E1-3039C8050C49). – sigy Aug 23 '23 at 13:44
12

It should be added that identifiers in quotation marks may contain special characters, e.g. "a-b c.d" is a valid identifier.

Erich Kitzmueller
  • 36,381
  • 5
  • 80
  • 102