When to use double quotes in Oracle column to avoid invalid identifier

I just created a table that has two columns, "notes_id" and "notes_name".

I then populated the tables from another table. When I run a query using

select * from notes_table

I see all the columns and all the data.

But, if I say

select notes_id from notes_table

it says I have an invalid identifier. I tried it on the other column name and got the same error. I even copied the column names from the create table ddl and it still gave me the error.

I am using Oracle 11.1.0.6. Can you help?

I’m guessing you included the double-quotes around the column names when you created the table? Since the column names are now lower-case, and Oracle always converts unquoted identifiers to upper-case, you must include double-quotes around the column names in your queries on this table. I.e., 

select "notes_id" from notes_table 

But I would recommend going back and recreating the table without the double-quotes. Or make the identifiers all upper-case if you insist on using double-quotes.

Have a question for Dan Clamage? Send an e-mail to [email protected]

.