Click here to Skip to main content
15,905,563 members
Articles / Programming Languages / SQL

Gotcha #1167: Quoted Identifiers in Oracle

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
16 Jun 2011CPOL2 min read 15.4K   3  
Quoted identifiers appear to be causing more problems and confusion than they are worth; not least the potential creation of "duplicate" column names.

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:

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

Now take a look at the following queries:

SQL
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:

SQL
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:

SQL
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:

SQL
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:

SQL
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:

A_COLUMNA_Column
1050Foo
2060Bar
3070Baz
SQL
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer
United Kingdom United Kingdom
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
-- There are no messages in this forum --