Oracle DB quote column names

Question

When using regular tables, its fine to use the following Oracle SQL query:

SELECT max(some_primary_key) FROM MyTable

However, when using Database Objects (i.e. a table of an object), this yields to the following error:

ORA-00904: "SOME_PRIMARY_KEY": invalid identifier

When quoting the column name, like this:

SELECT max("some_primary_key") FROM MyTable

This works like expected. Why is it necessary to escape column names when working with Objects, but not with Tables?

https://docs.oracle.com/database/121/SQLRF/sql_elements008.htm#SQLRF00223 — , Jul 12 '16 at 12:13

score 7 · Accepted Answer · answered Jul 12 '16 at 12:07

It doesn't have to do with objects or tables, it has to do with how these objects/tables have been created.

If you do create table "blabla" then you always need to address this table with "blabla", if you do create table blabla then you can address this table via BLABLA or blabla or bLabLa. Using " " makes the name case sensitive and that is the reason why most developers don't use " " because usually you don't want case sensitive names .

Thanks, this makes much more sense now :) – tobspr Jul 12 '16 at 12:30 — tobspr, Jul 12 '16 at 12:30

score 3 · Answer 2 · answered Jul 12 '16 at 12:20

Database Object Naming Rules

Every database object has a name. In a SQL statement, you represent the name of an object with a quoted identifier or a nonquoted identifier.

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.

A nonquoted identifier is not surrounded by any punctuation.

You can use either quoted or nonquoted identifiers to name any database object. However, database names, global database names, and database link names are always case insensitive and are stored as uppercase. If you specify such names as quoted identifiers, then the quotation marks are silently ignored. Refer to CREATE USER for additional rules for naming users and passwords.

To summarize this

When you do :

SELECT max(some_primary_key) FROM MyTable

Oracle assume that your column was declared like this :

CREATE TABLE MyTable (
    some_primary_key INT,
    ...
)

Seing the resulting error, it's not the case. You obviously declared it like this :

CREATE TABLE MyTable (
    "some_primary_key" INT,
    ...
)

And you should thus always refer to that column using double quotes and proper case, thus :

SELECT max("some_primary_key") FROM MyTable

The bible : Oracle Database Object Names and Qualifiers

score 2 · Answer 3 · answered Jul 12 '16 at 12:16

[TL;DR] The simplest thing to do is to never use double quotes around object names and just let oracle manage the case-sensitivity in its default manner.

Oracle databases are, by default, case sensitive; however, they will also, by default, convert everything to upper-case so that the case sensitivity is abstracted from you, the user.

CREATE TABLE Test ( column_name NUMBER );

Then:

SELECT COUNT(column_name) FROM test;
SELECT COUNT(Column_Name) FROM Test;
SELECT COUNT(COLUMN_NAME) FROM TEST;
SELECT COUNT(CoLuMn_NaMe) FROM tEsT;
SELECT COUNT("COLUMN_NAME") FROM "TEST";

Will all give the same output and:

DESCRIBE test;

Outputs:

Name        Null Type
----------- ---- ------
COLUMN_NAME      NUMBER

(Note: Oracle's default behaviour is to convert the name to upper case.)

If you use double quotes then oracle will respect your use of case in the object's name (and you are then required to always use the same case):

CREATE TABLE "tEsT" ( "CoLuMn_NaMe" NUMBER );

(Note: Both the table and column name are surrounded in double quotes and now require you to use exactly the same case, and quotes, when you refer to them.)

Then you can only do (since you need to respect the case sensitivity):

SELECT COUNT("CoLuMn_NaMe") FROM "tEsT";

And

DESCRIBE "tEsT";

Outputs:

Name        Null Type
----------- ---- ------
CoLuMn_NaMe      NUMBER

(Note: Oracle has respected the case sensitivity.)

Unfortunately some of my colums are named `at` and so they need to be escaped (I have no chance of changing their names) — tobspr, Jul 12 '16 at 12:29
@tobspr The normal response to this is to hunt down the developer who made that decision and inform them (vigorously) that using keywords and/or quotes in object names is bad practice and would they please refrain from doing it in the future... apart from that there is not much you can do other than keep using the expected case. — MT0, Jul 12 '16 at 12:40
This is legacy code from a few decades ago, which would be hard to change — tobspr, Jul 12 '16 at 13:08

score 1 · Answer 4 · answered Jul 12 '16 at 12:26

I created one object in Oracle 11g:

CREATE OR REPLACE TYPE MyType AS OBJECT (
   some_property NUMBER(20),
  CONSTRUCTOR FUNCTION MyType(some_property number default 123) RETURN SELF AS RESULT
) NOT FINAL;
/

CREATE OR REPLACE TYPE BODY MyType AS
  CONSTRUCTOR FUNCTION MyType(some_property number default 123)
    RETURN SELF AS RESULT
  AS
  BEGIN
    SELF.some_property := some_property;
    RETURN;
  END;
END;
/

---Created a table of my object

CREATE TABLE MYTABLE OF MYTYPE ;

---issued the statement.

SELECT max(some_property) FROM MYTABLE;

Its working fine for me without quotes. Not sure why its not working in your case. Whats your oracle version ?

As @RayCw mentioned, the issue was that I was creating the properties with quotes, e.g. `"some_property" NUMBER(20),` — tobspr, Jul 12 '16 at 12:28

Oracle DB quote column names

4 Answers4

Database Object Naming Rules

To summarize this

Linked