SQLGrammarException: ORA-00904 ("invalid identifier")

Question

I'm getting an Oracle error / org.hibernate.exception.SQLGrammarException when I run a JUnit test against a Java class which uses JPA and Spring:

ORA-00904: "ALIAS"."COLUMN_NAME": invalid identifier

The odd thing is that the JUnit test works when run against a development database but fails when run against a database which I use for Continuous Integration builds (they are both Oracle databases).

Therefore, this would suggest that there's something missing or awry in the latter d/b. However, I have double- (and triple-)checked that the table referred to is identical in both databases and contains the column COLUMN_NAME that the error is referring to.

Further information - the DAO Java class invoked in the JUnit test uses a javax.persistence.EntityManager:

MyClass myObject = entityManager.find(MyClass.class, idNumber);

The MyClass JPA entity class maps onto the Oracle table:

@Configurable
@Entity
@Table(name = "MyTable", schema = "MySchema")
public class MyClass implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "MY_SEQ")
    @SequenceGenerator(name = "MY_SEQ", sequenceName = "MY_SEQ", allocationSize = 1)
    @Column(name = "ID")
    protected BigDecimal id;

    @Column(name = "COLUMN_NAME")
    private String columnName;
    ...
}

The column COLUMN_NAME in the database table in both databases is VARCHAR2(50) and nullable.

Versions:

Java - 1.6

Spring - 3.1.1

Oracle - 11.2.0.3.0

Thanks in advance for any assistance.

Maybe they are not exactly the same - perhaps in one of the DBs the column was created with " which makes its name case-sensitive. See this sqlfiddle demo - the 2 tables look the same but one will give results and the other ORA-00904 — A.B.Cade
I've checked the script that creates the table and can confirm that there are no quotes used anywhere in it. I have also dropped the table and recreated it using this script, so I know that there is not an issue with quotation marks anywhere. — GarlicBread
I'm not familiar with JPA, but can you see the query which is generated ? can you show it to us ? — A.B.Cade
How does the table look like on database side? Can you execute desc MyTable on the database and post the result? — Johanna

GarlicBread GarlicBread · Accepted Answer · 2013-04-11T20:44:07

Found the problem. I found the query that Hibernate was creating (by adding

<property name="hibernate.show_sql" value="true" />

to my data-persistence.xml file) and tried to run it in the database where the test was failing. It transpires that in the entity Java class, the associated database table is defined like this:

@Table(name = "MyTable", schema = "MySchema")

... which works fine against the development d/b. However, the continuous integration database has all the tables created in a schema called something else, hence the error.

Thanks to everyone for their help.

SQLGrammarException: ORA-00904 ("invalid identifier")

2 Answers