Hibernate SQLException

Question

I want insert some data in one of my table, and I get next exception:

`java.sql.SQLException: ORA-02289: sequence does not exist`

Let's me to show my code. I have next classes:

@Entity
@Table(name="role")
public class Role implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO, generator="role_seq_gen")
    @SequenceGenerator(name="role_seq_gen", sequenceName="ROLE_SEQ")
    private Long roleId;

    @Column(name="role", unique=true)
    private String role;

    @ManyToMany(mappedBy = "roles")
    private List<Tipster> tipsters;

    // + getters and setters
}



@Entity
@Table(name="tipster")
public class Tipster implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO, generator="tipster_id_seq")
    @SequenceGenerator(name="tipster_id_seq", sequenceName="tipster_id_seq")
    @Column(name="tipsterId")
    private Long tipsterId;

    @NotEmpty
    @Column(name="username", unique=true)
    private String username;

    @NotEmpty
    @Column(name="email", unique=true)
    private String email;

    @NotEmpty
    @Column(name="password", unique=true)
    private String password;

    @Column(name="active")
    private int active;

    @ManyToMany
    @JoinTable
    private List<Role> roles;
    //+ getters and setters

}

This is a part of my application context code:

<context:annotation-config />

    <task:annotation-driven />

    <tx:annotation-driven transaction-manager="transactionManager" />


    <bean class="org.springframework.orm.jpa.JpaTransactionManager"
        id="transactionManager">
        <property name="dataSource" ref="dataSource" />
    </bean>

    <jpa:repositories base-package="com.gab.gsn.repository" />

    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
        destroy-method="close">
        <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
        <property name="url" value="jdbc:oracle:thin:@localhost:1521/XE" />
        <property name="username" value="gabrieltifui" />
        <property name="password" value="123321" />
    </bean>

    <bean
        class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"
        id="emf">
        <property name="packagesToScan" value="com.gab.gsn.entity" />
        <property name="dataSource" ref="dataSource" />
        <property name="jpaProperties">
            <props>
                <prop key="hibernate.format_sql">true</prop>
                <prop key="hibernate.use_sql_comments">true</prop>
                <prop key="hibernate.show_sql">true</prop>
                <prop key="hibernate.hbm2ddl.auto">create</prop>
                <prop key="hibernate.dialect">org.hibernate.dialect.OracleDialect</prop>
            </props>
        </property>
        <property name="persistenceProvider">
            <bean class="org.hibernate.jpa.HibernatePersistenceProvider" />
        </property>
    </bean>

Now, I try to insert a row in my Role table with this method:

@PostConstruct
    public void initDb(){
        Role role = new Role();
        role.setRole("User");
        roleRepository.save(role);
    }

When I my app on my Apache Server I get next exception:

Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:123)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:89)
    at org.hibernate.id.SequenceGenerator.generateHolder(SequenceGenerator.java:122)
    at org.hibernate.id.SequenceGenerator.generate(SequenceGenerator.java:115)
    at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:117)
    at org.hibernate.jpa.event.internal.core.JpaPersistEventListener.saveWithGeneratedId(JpaPersistEventListener.java:84)
    at org.hibernate.event.internal.DefaultPersistEventListener.entityIsTransient(DefaultPersistEventListener.java:206)
    at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:149)
    at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:75)
    at org.hibernate.internal.SessionImpl.firePersist(SessionImpl.java:811)
    at org.hibernate.internal.SessionImpl.persist(SessionImpl.java:784)
    at org.hibernate.internal.SessionImpl.persist(SessionImpl.java:789)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.persist(AbstractEntityManagerImpl.java:1181)
    ... 57 more
Caused by: java.sql.SQLException: ORA-02289: sequence does not exist

    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:113)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
    at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:754)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:219)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:813)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1051)
    at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:854)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1156)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3415)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3460)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:80)
    ... 68 more

It seems like I don't create the ROLE_SEQ sequence, but I know that Hibernate should create it automatically. Who can explain me why I get this exception?

Answer 1

It could be a problem related with your permissions, first execute the next statement select * from all_sequences where sequence_name = 'YOUR_SEQUENCE' ; If the sequence exists you only have to grant the permission to the user that you are using in your application. use grant select on YOUR_SEQUENCE to YOUR_USER; to fix your problem.