5

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?

Will
  • 2,858
  • 6
  • 33
  • 50

4 Answers4

9

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!

Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • or never use Oracle, if you find not using double quotes around identifiers in CREATE TABLE statements difficult for some reason! – Tony Andrews Jun 24 '11 at 14:02
  • 2
    Double quoted identifiers are SQL-92 standard! Always used quoted identifiers if you want to be sure instead of placing reliance on database magic converting characters. – ceving Dec 06 '12 at 13:43
  • 2
    @ceving Using double quoted identifiers as an organizational standard makes working with the database much more annoying, especially when you need to root around in the database directly. The *best* organizational standard is, "only use identifiers that don't *have* to be quoted, and then maybe quote them in code anyway." I'd much rather my database upgrade scripts blow up in my face if I used an identifier that requires quoting, though. – jpmc26 Jun 14 '16 at 19:01
  • 1
    @jpmc26 Also, the SQL 92 standard does not **mandate** double-quoted identifiers, it permits them - exactly as in Oracle! Because something is permitted does not mean it is advisable. – Tony Andrews Jun 15 '16 at 09:31
  • @jpmc26 First nobody on earth is able to say out of his head, which identifiers have to be quoted and second even if you look it up in the manual, it is still possible, that it will change with the next version of the dbms. If you want to be on the safe side, just use quotes. If you prefer a more dangerous life, dont use them. And if you want to distinguish between `person` and `Person`, as it is done in every modern programming language, you also have to use them. Treating `person` as `PERSON` is a a relic of bygone times, something appropriate for a DOS file system. – ceving Jun 16 '16 at 09:44
  • 3
    @ceving Are you **serious**? You **want** to have different tables in your database called `PERSON`, `person` and `Person`? It doesn't take long to learn that in Oracle you stick to names that begin with a letter and have nothing but letters, digits and underscores thereafter ($ and # are also permissible but discouraged). It takes all sorts I suppose... – Tony Andrews Jun 16 '16 at 13:15
  • 1
    @ceving While I can't tell you *all* of them without looking it up, I can *certainly* tell you that you're safe if you stick to upper case letters, numbers, and underscores in Oracle (without looking at the preceding comment!). (In PG, lowercase letters, numbers, and underscores.) KISS philosophy. – jpmc26 Jun 16 '16 at 16:02
3

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
Joel Slowik
  • 653
  • 5
  • 13
2

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

symcbean
  • 47,736
  • 6
  • 59
  • 94
2

On another, but similar note, the double quotes in Oracle indicate an identifier, not a string constant as the text you quoted indicates. This means that your example will give you an error even if "foo"."bar" exists. Consider this simple example:

SQL> SELECT "hello" FROM "DUAL";

SELECT "hello" FROM "DUAL"

ORA-00904: "hello": invalid identifier

versus this:

SQL> SELECT 'hello' FROM "DUAL";

'HELLO'
-------
hello

"DUAL" is an identifier: in this case it represents a table. It can represent a user, a column, etc., but it won't be interpreted as a string constant.

DCookie
  • 42,630
  • 11
  • 83
  • 92
  • 1
    It won't give an error if `"foo"."bar"` exists *and* has a column named `"hello"`, though. i'm pretty sure that's what the OP expected: the value of the column `"hello"`. – jpmc26 Jun 16 '16 at 16:24