Exception occurred while getting Hibernate sequence nextVal (Oracle)

Question

I am using spring boot and hibernate in my application and below is the configuration in Model class.

@Id
@SequenceGenerator(name="SCHEMA1.INQUIRY_QUEUE_SEQ", sequenceName="SCHEMA1.INQUIRY_QUEUE_SEQ",allocationSize=1)
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="SCHEMA1.INQUIRY_QUEUE_SEQ")
@Column(name="INQ_ID")
private Integer inquiryId;

Previously It was xml configuration for database mapping as below.

    <hibernate-mapping>
     <class name="com.company.domain.model.AnalystInquiryForm" table="INQUIRY_QUEUE" schema="SCHEMA1">
        <id name="inquiryId" type="java.lang.Integer">
        <column name="INQ_ID"/>
           <generator class="sequence">
             <param name="sequence">SCHEMA1.INQUIRY_QUEUE_SEQ</param>
           </generator>
        </id>
        <property name="transactionDate" type="java.sql.Timestamp">
          <column name="INQ_ADD_DT" />
        </property>
    </class>

    </hibernate-mapping>

Getting the below error

16:08:36.873 [http-nio-8551-exec-2] DEBUG org.hibernate.SQL - select schema1.inquiry_queue_seq.nextval from dual
16:08:37.419 [http-nio-8551-exec-2] WARN  org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 942, SQLState: 42000
16:08:37.431 [http-nio-8551-exec-2] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - ORA-00942: table or view does not exist


16:36:23.977 [http-nio-8551-exec-2] ERROR com.company.user.controller.UserController - Error in processAnalystInquiry : could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

And my spring boot configuration is as below.

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>1.5.1.RELEASE</version> 
    <relativePath />
</parent>
<dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>

    <dependency>
        <groupId>ojdbc</groupId>
        <artifactId>ojdbc15</artifactId>
        <version>11.2.0.2.0</version>
    </dependency>
    <dependency>
        <groupId>commons-lang</groupId>
        <artifactId>commons-lang</artifactId>
        <version>2.5</version>
    </dependency>
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-ehcache</artifactId>
    </dependency>

And middle layer is

@Autowired
private AnalystInquiryFormRepository analystInquiryFormRepository;
public void addToInquiryQueue(AnalystInquiryForm form) {
     analystInquiryFormRepository.save(form);
}

My repository is..

import org.springframework.data.repository.CrudRepository;
import com.company.user.model.AnalystInquiryForm;
public interface AnalystInquiryFormRepository extends 
CrudRepository<AnalystInquiryForm, Integer>{

}

are you sure that your schema1.inquiry_queue_seq exists in your DB? — Michal
– Michal, Commented May 28, 2018 at 11:09
this is not an exitsting table . It is autogenerated by hibernate. This is @SequenceGenerator name — surendrapanday
– surendrapanday, Commented May 28, 2018 at 11:13
yes but here sequenceName="SCHEMA1.INQUIRY_QUEUE_SEQ" you specify the name of the DB sequence, this sequence must exist in DB — Michal
– Michal, Commented May 28, 2018 at 11:17
if you you connect to DB (via some sql client) and call what hibernate does "select schema1.inquiry_queue_seq.nextval from dual". But I would expect that it is missing. you need to create it in DB. e.g. CREATE SEQUENCE INQUIRY_QUEUE_SEQ; — Michal
– Michal, Commented May 28, 2018 at 11:22

gargkshitiz · Accepted Answer · 2018-05-29 07:56:42Z

2

You are using

@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="SCHEMA1.INQUIRY_QUEUE_SEQ")

That means DB should have a table named: SCHEMA1.INQUIRY_QUEUE_SEQ.

If the table already exists, make sure that the user with which you are accessing this table, has all the appropriate permissions.

answered May 29, 2018 at 7:56

gargkshitiz

2,16817 silver badges21 bronze badges

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

1 Comment

surendrapanday Over a year ago

Right it was access issue. Worked after executing following query. GRANT SELECT ON SCHEMA1.INQUIRY_QUEUE_SEQ TO APUSER;

Sumit Anand · Accepted Answer · 2018-05-28 11:13:41Z

0

Either table or View does not exist or problem in your query, Check once whether query is accurate or not.

answered May 28, 2018 at 11:13

Sumit Anand

1081 silver badge5 bronze badges

1 Comment

surendrapanday Over a year ago

This is auto generated query from hibernate. This is @SequenceGenerator(name="SCHEMA1.INQUIRY_QUEUE_SEQ")

Collectives™ on Stack Overflow

Exception occurred while getting Hibernate sequence nextVal (Oracle)

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related