Oracle SQL Syntax: Quoted identifier

Question

I encountered SQL queries that looked like

select "hello"
from "foo"."bar"

I found that we can have quoted and unquoted identifiers in Oracle: Database Object Names and Qualifiers

... A quoted identifier begins and ends with double quotation marks ("). If you name a schema object using a quoted identifier, then you must use the double quotation marks whenever you refer to that object...

I asked the DBAs and they told me that there is a table with name bar but not "bar"

Why is that?

Answer 1

The table is named bar and not BAR or "bar" but because it is in lowercase you can only reference it using double quotes:

select * from bar; -- will fail

select * from "bar"; -- will succeed

The moral is: never create tables like this!

Answer 2

It helps to know that in the data_dictionary, object names are all stored capitalized; unless you use the quoted identifier to specifically tell oracle "hey, we wan't this objects name to be case sensitive" or in other words, "create this table with this literal name"

Below - create a table named bar, you can select using 'from bar', 'from BaR', 'from BAR'. 'from "bar"' wouldn't work because you are saying to oracle "give me the results from the table literally named lowercase "bar".

create a table named "bar", you can only select from it using 'from "bar"'. This is because 'from bar' translates to 'from BAR'.

SQL> create table bar (x varchar2(1));

Table created.

SQL> 
SQL> select * from bar;

no rows selected

SQL> select * from BAR;

no rows selected

SQL> select * from "bar";
select * from "bar"
              *
ERROR at line 1:
ORA-00942: table or view does not exist 


SQL> select * from "BAR";

no rows selected

SQL> 
SQL> drop table bar;

Table dropped.

SQL> 
SQL> create table "bar" (x varchar2(1));

Table created.

SQL> 
SQL> select * from bar;
select * from bar
              *
ERROR at line 1:
ORA-00942: table or view does not exist 


SQL> select * from BAR;
select * from BAR
              *
ERROR at line 1:
ORA-00942: table or view does not exist 


SQL> select * from "bar";

no rows selected

SQL> select * from "BAR";
select * from "BAR"
              *
ERROR at line 1:
ORA-00942: table or view does not exist 


SQL> 
SQL> drop table "bar";

Table dropped.

SQL> spool off

Answer 3

By quoting something it forces a non-collated match on a database entity. So I think Tony's answer is nearly right:

select sysdate from dual; -- works

select sysdate from DUAL; -- works

select sysdate from "DUAL"; -- works

select sysdate from "dual"; - FAILS

And as you quoted, if the table was created using a quoted string, then you will probably have to use a quoted string to reference it.

I've not experimented with Oracle, but on other DBMSs, using quoted identifiers allows you to use reserved words as identifiers, e.g. in MySQL (which uses a backquote rather than rabbit ears) the following would be valid:

SELECT `SUM` FROM `WHERE`;

HTH