Oracle identity - ORA-02289: sequence does not exist

Support
#1

Hi,

I’m having some issues with the Oracle 12c identity column.
I keep getting the error:ORA-02289: sequence does not exist.
Platform version: 7.02
Oracle version: 18c Enterprise Edition
Driver version: tested with ojdbc6 and ojdbc8 (same result)

It looks like the sequence generation between JPA and the database is not working with the identity column of Oracle.

This is the DDL preview generated by CUBA Studio:

create table COMPANY (
    ID number(10) generated by default on null as identity /** Supported from version 12c **/,
    CREATE_TS timestamp,
    CREATED_BY varchar2(50 char),
    UPDATE_TS timestamp,
    UPDATED_BY varchar2(50 char),
    --
    NAME varchar2(255 char) not null,
    --
    primary key (ID)
)^

The error logs give me this. Which is correct, because that sequence does not exist.

Internal Exception: java.sql.SQLSyntaxErrorException: ORA-02289: Reeks bestaat niet.
 
Error Code: 2289
Call: SELECT SEQ_GEN_IDENTITY.NEXTVAL FROM DUAL
Query: ValueReadQuery(sql="SELECT SEQ_GEN_IDENTITY.NEXTVAL FROM DUAL")

logs.txt (27.1 KB)

I was able to solve it by manually adding the ID column with @SequenceGenerator annotation.

@Table(name = "COMPANY")
@Entity(name = "oraclehr_Company")
public class Company extends BaseIntIdentityIdEntity implements Updatable, Creatable {

    @Id
    @SequenceGenerator(schema = "hr", name = "oraclehr_Company_seq", sequenceName  = "ISEQ$$_73886", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "oraclehr_Company_seq")
    @Column(name = "ID")
    protected Integer id;
 
    @NotNull
    @Column(name = "NAME", nullable = false)
    protected String name;
    ....
    }

This is not ideal because have to hard code all the sequenceNames in the source code.
And identity column generates a random sequence name, making it difficult to keep track.

I have also tried the old way of Oracle, with a sequence and a trigger. The Oracle HR sample database with sample data starts with ID=10. When creating new records with CUBA studio it starts with ID=1 until it reaches 10 and then of course gives unique constraint error.

Is there another way to fix this?
Is this an Oracle issue, eclipselink issue, JPA issue, CUBA issue?

UPDATE
The above workaround works if I create sequences manually and use the name in the source code.
Other issue, I can’t use the Entity Designer anymore, because I added the “id” field that belongs to “BaseIntIdentityIdEntity” into the Entity itself.

image

Is there anyway to fix this?

Renato

#3

No one else is having this issue with Oracle and CUBA?

#6

Hi Renato!

Unfortunately, Eclipselink does not support the Oracle 12c Identity column feature. You can find more information about the implementation of this feature here:
426852 – @GeneratedValue strategy=IDENTITY support in Oracle 12c.
So you can not use

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)

annotations with the Oracle database.

The only way to generate an ID for an BaseIntIdentityIdEntity is to create a sequence manually and specify its name in the source code (as you did in the workaround).

Regards,
Oleg.

#7

That’s what I expected. Thanks for the confirmation.
If anyone is looking for the working solution with CUBA Studio.
Create the sequence in Advance with a known name.
And before DB update, change the the CUBA generated script with the name.

-- create sequence manually
CREATE SEQUENCE COMPANY_ID_SEQ START WITH 1 NOCACHE ORDER ^

create table COMPANY (
    ID number(10) DEFAULT COMPANY_ID_SEQ.NEXTVAL not null,
    CREATE_TS timestamp,
    CREATED_BY varchar2(50 char),
    UPDATE_TS timestamp,
    UPDATED_BY varchar2(50 char),
    --
    NAME varchar2(255 char) not null,
    --
    primary key (ID)
)^

@Table(name = "COMPANY")
@Entity(name = "oraclehr_Company")
public class Company extends BaseIntIdentityIdEntity implements Updatable, Creatable {

    @Id
    @SequenceGenerator(schema = "hr", name = "COMPANY_ID_SEQ", sequenceName  = "COMPANY_ID_SEQ", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "COMPANY_ID_SEQ")
    @Column(name = "ID")
    protected Integer id;
 
    @NotNull
    @Column(name = "NAME", nullable = false)
    protected String name;
    ....
}