What exactly do quotation marks around the table name do?

Question

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?

Answer 1

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

Answer 2

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