ORA-02289: sequence does not exist, however sequence is aleady exist in database

Question

I have been working on a java spring project, in which I have created a class whose sole purpose is to record the request body for every service have been called in the database.

The table contains a primary key named as transaction_id and other fields which is required to records the request. The model of the tables in Java looks like:

@Entity
@Table(name="ForntEndLogHistory")
public class FrontEndLog {
    
    @Id
    @Column(name = "TRANSACTION_ID")
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "id_generator")
    @SequenceGenerator(name="id_generator", sequenceName = "id_seq2", allocationSize=1)
    private Long transactionId;
    private String request;
}

In the database, I have created one sequence named as id_seq2 whereas id_seq1 already exists:

Newly created id_seq2:

CREATE SEQUENCE  "id_seq2"  MINVALUE 0 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE ;
commit;

Already exists id_seq1 dll:

CREATE SEQUENCE  "id_seq1"  MINVALUE 0 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 3467 NOCACHE  NOORDER  NOCYCLE ;

case 1:

whenever I trigger any services from the postman with below sequence generator with sequence name id_seq2 I get the error "sequence does not exist". However, the sequence is already there in DB.

@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "id_generator")
    @SequenceGenerator(name="id_generator", sequenceName = "id_seq2", allocationSize=1)
    private Long transactionId;

The project war file is successfully deployed on the server. After that whenever the service is trigger from the postman following error is occurred:

org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:82)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:124)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:88)
    at org.hibernate.id.SequenceGenerator.generateHolder(SequenceGenerator.java:122)
    at org.hibernate.id.SequenceHiLoGenerator.generate(SequenceHiLoGenerator.java:73)
    at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:117)
    at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:206)
    at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:191)
    at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.performSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:114)
    at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:90)
    at org.hibernate.internal.SessionImpl.fireSaveOrUpdate(SessionImpl.java:654)
    at org.hibernate.internal.SessionImpl.saveOrUpdate(SessionImpl.java:646)
    at org.hibernate.internal.SessionImpl.saveOrUpdate(SessionImpl.java:642)
    at com.etisalat.mwallet.data.BaseDAO.save(BaseDAO.java:49)
    at com.etisalat.mwallet.service.impl.LoggerService.logFrontEnd(LoggerService.java:59)
    at com.etisalat.mwallet.service.impl.LoggerService.logFrontEndSuccess(LoggerService.java:29)
    at com.etisalat.mwallet.rest.config.ResponseFilter.doFilter(ResponseFilter.java:180)
    at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:79)
    at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.wrapRun(WebAppServletContext.java:3436)
    at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3402)
    at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
    at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:120)
    at weblogic.servlet.provider.WlsSubjectHandle.run(WlsSubjectHandle.java:57)
    at weblogic.servlet.internal.WebAppServletContext.doSecuredExecute(WebAppServletContext.java:2285)
    at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:2201)
    at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:2179)
    at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1572)
    at weblogic.servlet.provider.ContainerSupportProviderImpl$WlsRequestExecutor.run(ContainerSupportProviderImpl.java:255)
    at weblogic.work.ExecuteThread.execute(ExecuteThread.java:311)
    at weblogic.work.ExecuteThread.run(ExecuteThread.java:263)
Caused by: java.sql.SQLSyntaxErrorException: ORA-02289: sequence does not exist

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:774)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1111)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:4845)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1501)
    at weblogic.jdbc.wrapper.PreparedStatement.executeQuery(PreparedStatement.java:141)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79)

case 2:

Afterward for testing purpose, I have change the sequence name from id_seq2 to id_seq1 and redeployed the code on server and surprisely I am able to trigger the service sucessfully.

@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "id_generator")
    @SequenceGenerator(name="id_generator", sequenceName = "id_seq1", allocationSize=1)
    private Long transactionId;

Output:

{

"responseCode": "0"

}

I have no idea where I made a mistake as in my point of view, there is something which I have missed in sequence generator.

Answer 1

A missing sequence that exists may be caused from two reasons:

1) the sequence exists in a different schema that you are connected with

in this case you must reference schema_owner.schema_name

2) the sequence exists in a different schema and you have no privileges to access it

You should first realize in which schema the existing sequence is created.

You can verify it with the following query (substitute the sequence name as required)

select OWNER, OBJECT_NAME SEQUENCE_NAME
from all_objects where object_type = 'SEQUENCE' 
and lower(object_name) = 'id_seq1';

If you see nothing, the sequence either doesn't exists or it exists in a different schema as you are connected with and you have no SELECT priviledge on it.

In case you use a sequence from a different schema than your Hibernate connection user (which is rather a usual case), you must use the schema parameter of the @SequenceGenerator annotation and pass the propper schema owner.

Answer 2

In your case the sequence must exist in the same schema as the schema/user you log into. If you login as an other user then you can try this rather rude solution.

create public synonym "id_seq2" for "id_seq2";
grant select on "id_seq2" to public;

Or try to figure out what privs and synonyms you have om "id_seq1" and apply that to "id_seq2" as well.