4

I have an Oracle table with a column named with a reserved word (TYPE)

This is defined in the entity class as

@Entity
@Table(name="PROCESS_STORAGE")
.....
@Column(name="\"TYPE\"")
private String type;

But trying to retrieve a record from the DB causes an error:

2018-01-05 11:50:54.139  WARN 9340 --- [http-nio-8080-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 904, SQLState: 42000
2018-01-05 11:50:54.139 ERROR 9340 --- [http-nio-8080-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper   : ORA-00904: "PROCESSSTO0_"."type": invalid identifier
2018-01-05 11:50:54.144 ERROR 9340 --- [http-nio-8080-exec-3] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause

java.sql.SQLSyntaxErrorException: ORA-00904: "PROCESSSTO0_"."type": invalid identifier

Not quite sure what I'm missing here. It seems to be quoting the column name, but it's not finding it?

Edit I've just realised it's a case sensitivity issue - the column is uppercase in the DB (TYPE) and in the entity definition, but for some reason it's getting converted to lowercase in the query. Still not sure why this is happening though

Mick O'Hea
  • 1,619
  • 2
  • 14
  • 20
  • SQL keywords are case insensitive – Saravana Jan 05 '18 at 12:05
  • But it's not the keyword, it's the column name, which apparently is case sensitive in Oracle. I've confirmed by querying the DB directly: SELECT "type" FROM PROCESS_STORAGE; fails, SELECT "TYPE" FROM PROCESS_STORAGE; works – Mick O'Hea Jan 05 '18 at 12:08

3 Answers3

4

Think I've found a solution here

In application.properties, need to specify 

spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
Mick O'Hea
  • 1,619
  • 2
  • 14
  • 20
1

You need to enclose reserved keywords with backticks (`) for using it as column

TYPE is reserved in Oracle so it should be

@Column(name="`TYPE`")
private String type;
Saravana
  • 12,647
  • 2
  • 39
  • 57
  • Thanks, tried this but it's still converting the column name to lower case for the query and giving the same error – Mick O'Hea Jan 05 '18 at 12:09
0

If you are using Hibernate 3.5+, try:

hibernate.globally_quoted_identifiers=true to quote all database identifiers, this is added for JPA 2.0.

In JPA 2.0, the syntax is standardized and becomes:

@Column(name="\"TYPE\"")
justMe
  • 2,200
  • 16
  • 20