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 220.127.116.11. 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@example.com
Dig Deeper on Using Oracle PL-SQL
Related Q&A from Dan Clamage
A reader asks Oracle expert Dan Clamage about a single date condition mucking up a dynamic query. Continue Reading
One reader asks expert Daniel Clamage about the PL/SQL to_date and to_char functions and how to properly convert date and string values. Continue Reading
Oracle PL-SQL expert Daniel Clamage answers a question about a problem with doing an “insert” in PL-SQL to create a table dynamically. Continue Reading