Enclosing column names within double quoatation marks with CREATE command in Oracle doesn't work properly. Why?

Question

Lets consider a simple table say products in Oracle (I tried on Oracle 9i). I'm creating this table with the following CREATE statement.

CREATE TABLE products

("prod_id" varchar2(7) primary key, "product_name" varchar2(30) NOT NULL);

It is to be noted specially that I'm enclosing the column names within double quotation marks "" as not usually we do. It would obviously work and the products table would be created with those two columns with the specified CONSTRAINTS.

---

Now, lets insert some rows into this table using the following INSERT INTO command.

INSERT INTO products VALUES('P0001', 'Nokia-N97');
INSERT INTO products VALUES('P0002', 'Nokia-1208');
INSERT INTO products VALUES('P0003', 'Nokia-1115');

Would insert three rows into the products table.

---

To make sure that these rows have indeed inserted or not, we can issue a SELECT statement as follows.

SELECT * FROM products;

Would work just fine and display three rows we inserted.

---

Now, the actual question here. When we issue the following SELECT statement,

SELECT prod_id, product_name FROM products;

would not work even though we didn't make any mistake in this SQL. Oracle would report instead that such columns don't exist. Why does this happen? There must be very specific reason behind it, I think.

I'm sure that enclosing column names unnecessarily within double quotation marks as I have just done may not be the best practice but just a question occurred to me.

Answer 1

Against common believe, Oracle is case sensitive in column and table names. It just converts everything to upper case by default.

But if you use names in double quotes, you tell Oracle to create the column in the exact spelling you provided (lower case in the CREATE statement).

Since in the SELECT statement, you don't use quotes, the names are converted to upper case and are therefore not equal to the lower case names in the CREATE statement.

So this should work:

SELECT "prod_id", "product_name" FROM products;

If you don't know how column or table names are specified, you can look it up in the data dictionary. You will find lower case column names for your product table, but upper case table name, since it wasn't quoted.