CODEWITHSUNDEEP
oracle-databaseoracle11goracle11gr2
pheromix
pheromix

Reputation: 19317

Invalid identifier although the column is in the table

The database is Oracle 11g release2.

There is a Spring application making connection to the above database. All the mappings are correct. At runtime when submitting a form to update a record then I get the exception invalid identifier : some_column. Although the column is in the table when I make a desc command on it through SqlPlus ! So how to fix that ?

Upvotes: 1

Views: 2420

Answers (2)

user5683823
user5683823

Reputation:

By default, identifiers (names of schemata, tables, columns, procedures, etc.) are stored in all-upper-case in the data catalog. You can write them any way you want, and you can use them any way you want in any part of your queries, functions etc.; the parser (pre-processor) re-writes them in upper-case.

This doesn't mean, though, that Oracle is case-insensitive. It is not. You may require that capitalization be preserved, by enclosing the identifier in double quotes. You did that; you probably thought it means something else.

If you define a column as id, ID, or Id, you can then call it by any of the three names and you will still get the desired result. However, if you call a column "ID", you must then refer to it as "ID", not "id" or "Id", every time you reference it. (You may, however, refer to it as id, without the double quotes, since the parser will map that to ID, which is the same as "ID".)

In fact, you can have two columns in the same table, one called "ID" and the other "id" - there is no conflict. They may contain different data - they may even be of different data type. However, you cannot have "ID" and ID (or "ID" and id - because Oracle automatically maps id to ID, as I said already).

In your case, the simple solution is, drop the double-quotes. Get in the habit of NEVER, EVER using double-quoted names; that will save you a lot of trouble.

Upvotes: 3

Dr Y Wit
Dr Y Wit

Reputation: 2020

If your application encloses identifies into double quotes then case matters.

create table t(id1 int, id2 int);

Table created.

UPDATE T SET ID1 = 1, ID2 = 2;

0 rows updated.

update t set id1 = 1, id2 = 2;

0 rows updated.

UPDATE T SET "ID1" = 1, ID2 = 2;

0 rows updated.

update t set "id1" = 1, id2 = 2;
update t set "id1" = 1, id2 = 2
             *
ERROR at line 1:
ORA-00904: "id1": invalid identifier

Upvotes: 3

Related Questions