Collectives™ on Stack Overflow
Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
When I open up the table in sqldeveloper, the column is present.
The error is only happening in PROD, not in DEV.
What should I check?
ORA-00904-invalid identifier
errors are frequently caused by case-sensitivity issues. Normally, Oracle tables and columns are not case sensitive and cannot contain punctuation marks and spaces. But if you use double quotes to create a quoted identifier, that identifier must always be referenced with double quotes and with the correct case. For example:
create table bad_design("goodLuckSelectingThisColumn " number);
–
It is because one of the DBs the column was created with " which makes its name case-sensitive.
Oracle Table Column Name : GoodRec
Hive cannot recognize case sensitivity : ERROR thrown was -
Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "GOODREC": invalid identifier
Solution : Rename Oracle column name to all caps.
check the position of Column annotation in java class for the field
For Example,consider one table with name STUDENT with 3 column(Name,Roll_No,Marks).
Then make sure you have added below annotation of column before Getter Method instead of Setter method. It will solve ur problem
@Column(name = "Name", length = 100)
**@Column(name = "NAME", length = 100)
public String getName() {**
return name;
public void setName(String name) {
this.name= name;
Have you compared the table definitions in Prod and Dev?
And when you are running it in SQL Developer, are you running the query in Prod (same database as the application) and with the same user?
If there are some additional columns that you are adding (using an alter command) and these changes are not yet promoted to prod, this issue is possible.
Can you post the definition of the table and your actual Query?
Seems like Oracle is throwing this error in many cases.
For me it was thrown, because I tried to qualify a column that was used in using
clause in the join part. None of there two will work:
select table1.x -- doesn't work
from table1
join table2 using (x);
select t1.x -- doesn't work
from table1 t1
join table2 t2 using(x);
It's because we can qualify the column from using
clause with neither table name nor alias. The correct way would be:
select x
from table1
join table2 using (x);
select x
from table1 t1
join table2 t2 using(x);
I was seeing this error for a slightly different reason to those given above:
I'd used a liquibase script to modify my already-existing (Oracle) table. Everything looked fine but the inserts in my code were failing. Only when I saw that I was being offered enclosing quotes by SQL Developer's auto-completion did I see that a space had been appended to the column name at creation.
The offending liquibase changeSet line looked like this (single space before the 'MY_FIELD' closing quote):
<addColumn tableName="MY_TABLE">
<column name="MY_FIELD " type="tinyint" defaultValueNumeric="3">
<constraints nullable="false"/>
</column>
</addColumn>
–
I use Toad for Oracle and if the table is owned by another username than the one you logged in as and you have access to read the table, you still may need to add the original table owner to the table name.
For example, lets say the table owner's name is 'OWNER1' and you are logged in as 'USER1'. This query may give you a ORA-00904 error:
select * from table_name where x='test';
Prefixing the table_name with the table owner eliminated the error and gives results:
select * from
Its due to mismatch between column name defined in entity and the column name of table (in SQL db )
java.sql.SQLException: ORA-00904: "table_name"."column_name": invalid identifier
e.g.java.sql.SQLException: ORA-00904: "STUDENT"."NAME": invalid identifier
issue can be like
in Student.java(entity file)
You have mentioned column name as "NAME" only.
But in STUDENT table ,column name is lets say "NMAE"
Check for synonym declaration in Oracle. I was calling an PL/SQL function as below and it was giving java.sql.SQLSyntaxErrorException: ORA-00904: invalid identifier
select oracle_my_pkg.notify_list_function from dual
after declaring a public synonym for oracle_my_pkg the error message was gone
create a new table.
but don't give the values in "(double quotes)
that's look like only solution
create table bad_design(goodLuckSelectingThisColumn number);
insted of
create table bad_design("goodLuckSelectingThisColumn " number);
–
Check the username credential used to login to the database. (persistence.xml ??). The problem mostly is, the username\password used to login to the database, does not have visiblity to the object (table_name in this case). ( try logging in to sql developer, using the same username\password available in your data source)
–
–
–
–
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.