Problem whit add sequence in Oracle and Spring

Question

I get this error when I try add new record

Hibernate:
    select
        STEST_PP_DATA_SEQ.nextval
    from
        dual
2022-04-06 12:53:03.487  WARN 23884 --- [0.1-8082-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 2289, SQLState: 42000
2022-04-06 12:53:03.510 ERROR 23884 --- [0.1-8082-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : ORA-02289: sequence does not exist

My Class

@Data
@NoArgsConstructor
@AllArgsConstructor
@Entity(name = "TEST_PP_DATA")
@SequenceGenerator(name = "STEST_PP_DATA_SEQ", sequenceName = "STEST_PP_DATA_SEQ", initialValue = 1, allocationSize = 1)
public class TestPPData extends AuditableEntity{

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "STEST_PP_DATA_SEQ")
@Column(name = "ID", nullable = false)

This how I add sequence to my DB

CREATE SEQUENCE STEST_PP_DATA_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE MINVALUE 1 NOCYCLE NOCACHE NOORDER

Gnqz · Accepted Answer · 2022-04-06 11:32:10Z

2

Probably you need to fully qualify the name. Can you try the following query to retrieve the owner:

SELECT owner, status
  FROM All_Objects
 WHERE object_type = 'SEQUENCE'
   AND object_name = 'STEST_PP_DATA_SEQ';

and then use the sequence as:

select <OWNER>.STEST_PP_DATA_SEQ.nextval
  from dual;

and if the first query doesn't return anything or the status is different from VALID, then you have a problem in your sequence creation.

answered Apr 6, 2022 at 11:32

Gnqz

3,3823 gold badges28 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dev007 Over a year ago

This query correctly returns the owner and the status = Valid. I can up (nextval) the number but only from the database client.

Gnqz Over a year ago

Can you try to following: CREATE PUBLIC SYNONYM STEST_PP_SEQ for OWNER.STEST_PP_DATA_SEQ and then SELECT STEST_PP_SEQ.nexval FROM DUAL in your code.

Dev007 Over a year ago

I already know why it doesn't work. You were right. I am new to the project and it turns out that this one of user does not have full access to one of the databases

Collectives™ on Stack Overflow

Problem whit add sequence in Oracle and Spring

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related