JPA : Auto Increment ID with Oracle Database

Question

I define an Entity with a Generated Id value, but when I try to do an insert I got this error :

    10:33:32,202 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-8) SQL Error: 2289, SQLState: 42000
    10:33:32,203 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-8) ORA-02289: sequence does not exist
    10:33:32,205 ERROR [org.jboss.as.ejb3.invocation] (default task-8) WFLYEJB0034: EJB Invocation failed on component  javax.ejb.EJBTransactionRolledbackException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
Caused by: java.sql.SQLSyntaxErrorException: ORA-02289: sequence does not exist

Entity :

@Entity
@Table(name = "TYPEDEM")
public class TypeDem {
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name = "ID")
    private long id;
    @Column(name = "CODE")
    private String code;
    @Column(name = "LABEL")
  ..
}

Claass :

@Override
    public TypeDem save(TypeDem typeDem) {
        entityManager.persist(typeDem);
        return typeOfDemand;
    }

In debug, I see that the value of id is null

see https://stackoverflow.com/questions/20603638/what-is-the-use-of-annotations-id-and-generatedvaluestrategy-generationtype — Scary Wombat, Aug 14 '17 at 08:40
Nope, Oracle does NOT have "auto-increment" support, so no you cannot use IDENTITY strategy with it. I would however have expected your JPA provider to tell you that, rather than just using NULL! Perhaps you should raise a bug on it. — Neil Stockton, Aug 14 '17 at 08:48
what is the sequence which you want to use for generating id. Is it created in your DB schema — Harish Barma, Aug 14 '17 at 08:50
as to my knowledge, oracle supports IDENTITY Generation Type from version 12c onwards. The Oracle sever version I'm using is 21c and IDENTITY works fine. — Dilini Peiris, Apr 06 '22 at 14:13

score 1 · Answer 1 · answered Aug 14 '17 at 08:46

identity generator

It is used in Sybase, My SQL, MS SQL Server, DB2 and HypersonicSQL to support the id column. The returned id is of type short, int or long.

The IDENTITY type (included in the SQL:2003 standard) is supported by:

SQL Server MySQL (AUTO_INCREMENT) DB2 HSQLDB The IDENTITY generator allows an integer/bigint column to be auto-incremented on demand. The increment process happens outside of the current running transaction, so a roll-back may end-up discarding already assigned values (value gaps may happen).

For Oracle, Use the below code @GeneratedValue(strategy=GenerationType.AUTO)

@Id
@GeneratedValue(strategy=GenerationType.AUTO)
@Column(name = "ID")

Thx , but when I use AUTO I got the same error – user1814879 Aug 14 '17 at 08:52 — user1814879, Aug 14 '17 at 08:52

score 0 · Answer 2 · answered Aug 14 '17 at 08:53

0

Your solution is correct.

The only issues seems to be hibernate.hbm2ddl.auto not set to update, Please use update. Be very careful while using create or create-drop.

Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
Caused by: java.sql.SQLSyntaxErrorException: ORA-02289: sequence does not exist

This clearly suggests the sequence is missing in the database.

answered Aug 14 '17 at 08:53

user1615664

591
2
11
24

2

His "solution" is not correct. He selected IDENTITY strategy. IDENTITY is to represent an "identity column" ... in RDBMS parlance this is AUTO_INCREMENT, or SERIAL column type. Oracle has no such support. The JPA spec also says "IDENTITY is not portable across all datastores". If he wants to use a sequence he should select SEQUENCE strategy – Neil Stockton Aug 14 '17 at 09:48

JPA : Auto Increment ID with Oracle Database

2 Answers2