Gotcha #1167: Quoted Identifiers in Oracle

Introduction

It's been many years since I last worked with Oracle in anger, and having had to recently work with it again, it's amazing how many of its "features" (I use the term guardedly) have either appeared in the intervening years, or (more likely) I had forgotten about altogether.

However, this one had me (and a few others in the office, I might add) puzzled for an afternoon, and once again, the documentation for this is scant (or at the very least, hard to find).

The Scenario

Consider the following table:

CREATE TABLE A_TABLE 
(
   A_COLUMN NUMBER(5,0) NOT NULL ENABLE
)

Now take a look at the following queries:

SELECT A_COLUMN FROM A_TABLE -- Returns the data
SELECT A_Column FROM A_TABLE -- Returns the data

Nothing magical there. The query returns the data correctly regardless of the case used in the query, exactly as we would expect.

Now consider this table:

CREATE TABLE ANOTHER_TABLE
(
   "A_Column" NUMBER(5,0) NOT NULL ENABLE
)

When we query this table in the same way, the result is very different:

SELECT A_COLUMN FROM ANOTHER_TABLE -- ORA-00904: "A_COLUMN": invalid identifier
SELECT A_Column FROM ANOTHER_TABLE -- ORA-00904: "A_COLUMN": invalid identifier

As you can see, Oracle now complains that it can't find the specified column, even when we have specified the column name with the correct case.

The Problem

The problem is caused by the fact that not only is Oracle case-sensitive, but it also implicitly converts all identifiers to upper case. This is why when querying A_TABLE, the case of the query doesn't matter. However, when querying ANOTHER_TABLE, the query fails as the identifier is always converted to A_COLUMN, whereas the column is actually named A_Column.

To prevent Oracle converting identifiers to upper case, they must be enclosed in double-quotes ('"'), just as when the table was created. Therefore, the following query will work:

SELECT "A_Column" FROM ANOTHER_TABLE

OK, fair enough, it's not ideal but we can live with that. However, this in itself presents another interesting scenario. Consider this table:

CREATE TABLE YET_ANOTHER_TABLE
(
   "A_COLUMN" NUMBER(5,0) NOT NULL ENABLE, 
   "A_Column" VARCHAR2(100) NOT NULL ENABLE
)

Yes, in Oracle, this is a perfectly valid (if not recommended) table definition! So assuming the following data, what happens when we query it:

SELECT A_COLUMN FROM YET_ANOTHER_TABLE -- Returns 1050, 2060 and 3070
SELECT "A_COLUMN" FROM YET_ANOTHER_TABLE -- Returns 1050, 2060 and 3070
SELECT A_Column FROM YET_ANOTHER_TABLE -- Returns 1050, 2060 and 3070
SELECT "A_Column" FROM YET_ANOTHER_TABLE -- Returns Foo, Bar and Baz

The Moral of the Story

This clearly highlights the need for robust database standards, part of which must include whether or not to use quoted identifiers and follow that decision rigidly throughout your database. My personal recommendation would be against the use of quoted identifiers as they appear to cause more problems and confusion than they are worth; not least the potential creation of "duplicate" column names.